Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How do I assign values to several variables in a sql select statement
Posted on 2011-02-25
I created a trigger and need to assign values to multiple variabes. I am getting errors when I execute the statement.
I get the following error
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 19
Incorrect syntax near the keyword 'Select'.
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 26
Incorrect syntax near the keyword 'from'.
What am I doing wrong here?
--SCRIPT
CREATE TRIGGER Update_Payments
ON Sales AFTER INSERT
AS
BEGIN
DECLARE @CurrentOrderID int
DECLARE @CurrentPublisherID [char](10)
DECLARE @CurrentISBN [char](20)
DECLARE @CurrentContactID [char](10)
DECLARE @CurrentDate datetime
DECLARE @RoyaltyAmount money
SET NOCOUNT ON;
SET @CurrentOrderID = Select MAX(OrderID) from Sales
Select @CurrentPublisherID = P.PubisherID,
@CurrentISBN = S.ISBN,
@CurrentContactID = R.ContactID,
@RoyaltyAmount = S.SaleAmount * R.rate/100,
@CurrentDate = S.Date,
from Publishers P,Sales S,Royalty R where SOrderID = @CurrentOrderID
and S.ISBN = Publishers.ISBN and R.ISBN = S.ISBN
Insert into payments Values(@CurrentPublisherID
SET NOCOUNT OFF;
END
GO
I get the following error
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 19
Incorrect syntax near the keyword 'Select'.
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 26
Incorrect syntax near the keyword 'from'.
What am I doing wrong here?
--SCRIPT
CREATE TRIGGER Update_Payments
ON Sales AFTER INSERT
AS
BEGIN
DECLARE @CurrentOrderID int
DECLARE @CurrentPublisherID [char](10)
DECLARE @CurrentISBN [char](20)
DECLARE @CurrentContactID [char](10)
DECLARE @CurrentDate datetime
DECLARE @RoyaltyAmount money
SET NOCOUNT ON;
SET @CurrentOrderID = Select MAX(OrderID) from Sales
Select @CurrentPublisherID = P.PubisherID,
@CurrentISBN = S.ISBN,
@CurrentContactID = R.ContactID,
@RoyaltyAmount = S.SaleAmount * R.rate/100,
@CurrentDate = S.Date,
from Publishers P,Sales S,Royalty R where SOrderID = @CurrentOrderID
and S.ISBN = Publishers.ISBN and R.ISBN = S.ISBN
Insert into payments Values(@CurrentPublisherID
SET NOCOUNT OFF;
END
GO
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
- +1
6 Comments
Thanks derek. I made that correction and now I am getting this error
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 19
Incorrect syntax near the keyword 'Select'.
i cant see anything wrong
Msg 156, Level 15, State 1, Procedure Update_Payments, Line 19
Incorrect syntax near the keyword 'Select'.
i cant see anything wrong
i think it might be this:
SET @CurrentOrderID = Select MAX(OrderID) from Sales
try
SET @CurrentOrderID = (Select MAX(OrderID) from Sales)
or simply
Select @CurrentOrderID = MAX(OrderID) from Sales
SET @CurrentOrderID = Select MAX(OrderID) from Sales
try
SET @CurrentOrderID = (Select MAX(OrderID) from Sales)
or simply
Select @CurrentOrderID = MAX(OrderID) from Sales
Please don't take it the wrong way, but let me suggest you read up on TRIGGERs and in particular the INSERTED logical tables.
Please see this Stored Procedure. I have commented the error spots and corrected.
CREATE TRIGGER Update_Payments
ON Sales AFTER INSERT
AS
BEGIN
DECLARE @CurrentOrderID int
DECLARE @CurrentPublisherID [char](10)
DECLARE @CurrentISBN [char](20)
DECLARE @CurrentContactID [char](10)
DECLARE @CurrentDate datetime
DECLARE @RoyaltyAmount money
SET NOCOUNT ON;
-- Error #1:- Incorrect syntax near the keyword 'Select'
-- SET = Select MAX(OrderID) from Sales
Select @CurrentOrderID = MAX(OrderID) from Sales
Select @CurrentPublisherID = P.PubisherID,
@CurrentISBN = S.ISBN,
@CurrentContactID = R.ContactID,
@RoyaltyAmount = S.SaleAmount * R.rate/100,
@CurrentDate = S.Date -- , Error#2:- Incorrect syntax near the keyword 'from'.
from Publishers P,Sales S,Royalty R where SOrderID = @CurrentOrderID
and S.ISBN = Publishers.ISBN and R.ISBN = S.ISBN
Insert into payments Values(@CurrentPublisherID,@CurrentISBN,@CurrentContactID,@RoyaltyAmount,@CurrentDate,'Scheduled')
SET NOCOUNT OFF;
END
GO
Featured Post
Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 11 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.