Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-25
6
Medium Priority
?
358 Views
Last Modified: 2012-05-11
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
Comment
Question by:wademi
[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
6 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 34985288
on your @CurrentDate = S.Date line, you need to remove the  comma
0
 

Author Comment

by:wademi
ID: 34985320
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
 

Author Comment

by:wademi
ID: 34985334
I think there is something wrong with the part of the query where I am assigning the variables
0
Concerto's Cloud Advisory Services

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.

 
LVL 15

Accepted Solution

by:
derekkromm earned 1600 total points
ID: 34985469
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34985643
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
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 400 total points
ID: 34985878
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

Free learning courses: Active Directory Deep Dive

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…

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.

Join & Ask a Question