Error in stored procedure

Question

Post reply

Error in stored procedure

baiju krishnan

SSC Veteran

Points: 288
More actions
January 15, 2015 at 4:28 am

#313750

Hi following is my stored procedure
create procedure [dbo].[Sp_Displayshift]
@StDt Datetime,
@EnDt datetime ,
@shifttype nvarchar(50)
as
DECLARE @query AS NVARCHAR(MAX);
;WITH Dates AS(
SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date
FROM (VALUES(0),(0),(0),(0),(0),(0))E(N),
(VALUES(0),(0),(0),(0),(0),(0))E2(N)
)
SELECT @Query = 'SELECT m.empno ' + CHAR(13)
+ (SELECT CHAR(9) + ',MAX( CASE WHEN sdate = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13)
FROM Dates
WHERE Month_date BETWEEN @StDt AND @EnDt
ORDER BY Month_date
FOR XML PATH(''),TYPE).value('.','varchar(max)')
+ '
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno where b.shifttype=@shifttype
GROUP BY m.empno'
EXEC sp_executesql @Query
when i execute like this
exec Sp_Displayshift '2015-01-01','2015-01-15','MS'
following error occurs
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@shifttype".
how to solve this
Regards
Baiju

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Baiju

Try enclosing your parameter list in parentheses.

John

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Since you are using dynamic SQL in your sp, you cannot simply use the parameter @shifttype, because it is not known in the execution context of the EXEC command. In other words, you do not define @shifttype in your dynamic SQL.

However, you can pass parameters to the dynamic SQL with sp_executesql:

Passing parameters to and from dynamic queries

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

or resolve it in the string:

+ '

FROM employee m

LEFT JOIN shiftdetails b ON m.empno = b.empno

where b.shifttype = ''' + @shifttype +

''' GROUP BY m.empno'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

This is because sp_executesql runs the query in a separate context where no variables from calling context are available.

See BOL for how to declare and pass parameters for dynamic sql with sp_executesql .

http://msdn.microsoft.com/en-us/library/ms188001(v=sql.100).aspx

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

ChrisM@Work (1/15/2015)
or resolve it in the string:
+ '
FROM employee m
LEFT JOIN shiftdetails b ON m.empno = b.empno
where b.shifttype = ''' + @shifttype +
''' GROUP BY m.empno'

That's an option as well.

I have also used something like this before:

SET @Query = REPLACE(@Query,'@shifttype',@shifttype);

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Koen Verbeeck (1/15/2015)
ChrisM@Work (1/15/2015)
or resolve it in the string:
+ '
FROM employee m
LEFT JOIN shiftdetails b ON m.empno = b.empno
where b.shifttype = ''' + @shifttype +
''' GROUP BY m.empno'
That's an option as well.
I have also used something like this before:
SET @Query = REPLACE(@Query,'@shifttype',@shifttype);

That would work nicely for a nonstring data type.

I wonder why the OP marked this post?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

ChrisM@Work (1/15/2015)
Koen Verbeeck (1/15/2015)
ChrisM@Work (1/15/2015)
or resolve it in the string:
+ '
FROM employee m
LEFT JOIN shiftdetails b ON m.empno = b.empno
where b.shifttype = ''' + @shifttype +
''' GROUP BY m.empno'
That's an option as well.
I have also used something like this before:
SET @Query = REPLACE(@Query,'@shifttype',@shifttype);
That would work nicely for a nonstring data type.
I wonder why the OP marked this post?

Because it's awesome of course 🙂

But yeah, you have to be careful with quotes are you mess up the SQL statement.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP