Error creating a stored procedure

n2dweb
n2dweb used Ask the Experts™
on
Receiving  

Msg 102, Level 15, State 1, Procedure aaaTestDed, Line 15
Incorrect syntax near '@RD'.

when creating this stored procedure.
CREATE PROCEDURE [dbo].[aaaTestDed]
        -- Add the parameters for the stored procedure here
        @RN AS varchar(255)
AS
BEGIN
Declare @CN as varchar(50);
Declare @RD as Datetime;
SELECT @RD = (SELECT CDate FROM PRun WHERE RunNum = @RN);
SELECT @CN = (SELECT CNum FROM PRun WHERE RunNum = @RN);
 
SELECT  PR.CNum, PR.PayCDate, PRV.EmpKey, PRD.DedNum
FROM  dbo.PRun AS PR INNER JOIN
      dbo.PRunVoucher AS PRV ON PR.RunNum = PRV.RunNum INNER JOIN
      dbo.PRunDeductions AS PRD ON PRV.VN = PRD.VN
WHERE  PR.CNum = @CN AND PR.CDate = @RD

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
You need an END statement at the end of the process
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
n2dweb,

Looks like Tim already posted the main suggestion.  I would also recommend turning on NOCOUNT and note you can get both the variables in one SELECT as shown.

Regards,
isa
CREATE PROCEDURE [dbo].[aaaTestDed]
        -- Add the parameters for the stored procedure here
        @RN AS VARCHAR(255)
AS
BEGIN
DECLARE @CN as VARCHAR(50);
DECLARE @RD as DATETIME;
 
SET NOCOUNT ON;
 
SELECT @RD = CDate, @CN = CNum FROM PRun WHERE RunNum = @RN;
 
SELECT PR.CNum, PR.PayCDate, PRV.EmpKey, PRD.DedNum
FROM dbo.PRun AS PR 
INNER JOIN dbo.PRunVoucher AS PRV ON PR.RunNum = PRV.RunNum 
INNER JOIN dbo.PRunDeductions AS PRD ON PRV.VN = PRD.VN
WHERE PR.CNum = @CN AND PR.CDate = @RD;
END
GO

Open in new window

try casting @RD as Date (rather than DateTime) before comparing with CDate
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
There is no data type DATE in MS SQL. ;)
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
At least not until you get to MS SQL Server 2008. :)
n2dwebprogrammer

Author

Commented:
Man am I slow ;-(
just di d not see it....

The "End"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial