Solved

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

Posted on 2011-02-25
6
342 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 15

Accepted Solution

by:
derekkromm earned 400 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 100 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Running total between 2 sql tables in Sql 6 50
Best way to get data into a database 12 56
sql trace 4 28
SqlServer amend PK column 5 11
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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