Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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