Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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
Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month7 days, 12 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.