• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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
0
wademi
Asked:
wademi
2 Solutions
 
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
 
wademiAuthor Commented:
I think there is something wrong with the part of the query where I am assigning the variables
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now