How do I assign values to several variables in a sql select statement

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,@CurrentISBN,@CurrentContactID,@RoyaltyAmount,@CurrentDate,'Scheduled')
                           
SET NOCOUNT OFF;  

END
GO
wademiAsked:
Who is Participating?
 
derekkrommCommented:
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
0
 
derekkrommCommented:
on your @CurrentDate = S.Date line, you need to remove the  comma
0
 
wademiAuthor Commented:
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
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
wademiAuthor Commented:
I think there is something wrong with the part of the query where I am assigning the variables
0
 
Anthony PerkinsCommented:
Please don't take it the wrong way, but let me suggest you read up on TRIGGERs and in particular the INSERTED logical tables.
0
 
Rajkumar GsSoftware EngineerCommented:
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 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.