Monitor your entire network from a single platform. Free 30 Day Trial Now!
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
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
Who is Participating?
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
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
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.
SET @CurrentOrderID = Select MAX(OrderID) from Sales
try
SET @CurrentOrderID = (Select MAX(OrderID) from Sales)
or simply
Select @CurrentOrderID = MAX(OrderID) from Sales