Dynamic sql Error message when executing a Stored procedure

Question

Dynamic sql Error message when executing a Stored procedure

meo2

Valued Member

Points: 70
More actions
July 13, 2009 at 3:37 pm

#208121

Hi,
I am trying to execute a paramtrized stored procedure, it works fine when @IncludeDeleted = 1
but it gives an error message when it is set to 0
"Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@xDeleted".
(1 row(s) affected)"
The sql code is as follows:
ALTER PROCEDURE [dbo].[sp_getDeviceList]
@IncludeDeleted bit = 0
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlStatement nvarchar(1000),
@SqlParams nvarchar(1000)
SELECT @SqlStatement =
'SELECT [DeviceId]
,[OtherDeviceId]
,[Comments]
,Device.[Deleted] AS Deleted
FROM [ServerDB].[dbo].[Device], [ServerDB].[dbo].[DCD_DeviceType]
Where 1 = 1 AND Device.DeviceType = DCD_DeviceType.DeviceTypeID'
IF @IncludeDeleted = 0
BEGIN
SELECT @SqlStatement = @SqlStatement + ' AND Device.[Deleted] = @xDeleted ORDER BY [DeviceName]'
SELECT @SqlParams = '@xDeleted bit '
EXEC sp_executesql @SqlStatement,@SqlParams,@IncludeDeleted
END
ELSE
SELECT @SqlStatement = @SqlStatement + ' ORDER BY [DeviceName]'
EXEC sp_executesql @SqlStatement
What AM I missing here?
Thanks for the help

Viewing 3 posts - 1 through 3 (of 3 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

meo2 (7/13/2009)
Hi,
I am trying to execute a paramtrized stored procedure, it works fine when @IncludeDeleted = 1
but it gives an error message when it is set to 0
"Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@xDeleted".
(1 row(s) affected)"
The sql code is as follows:
ALTER PROCEDURE [dbo].[sp_getDeviceList]
@IncludeDeleted bit = 0
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlStatement nvarchar(1000),
@SqlParams nvarchar(1000)
SELECT @SqlStatement =
'SELECT [DeviceId]
,[OtherDeviceId]
,[Comments]
,Device.[Deleted] AS Deleted
FROM [ServerDB].[dbo].[Device], [ServerDB].[dbo].[DCD_DeviceType]
Where 1 = 1 AND Device.DeviceType = DCD_DeviceType.DeviceTypeID'
IF @IncludeDeleted = 0
BEGIN
SELECT @SqlStatement = @SqlStatement + ' AND Device.[Deleted] = @xDeleted ORDER BY [DeviceName]'
SELECT @SqlParams = '@xDeleted bit '
EXEC sp_executesql @SqlStatement,@SqlParams,@IncludeDeleted
END
ELSE
SELECT @SqlStatement = @SqlStatement + ' ORDER BY [DeviceName]'
EXEC sp_executesql @SqlStatement
What AM I missing here?
Thanks for the help

Just the declaration of the variable:

IF @IncludeDeleted = 0

BEGIN

SELECT @SqlStatement = 'declare @xDeleted bit;' + @SqlStatement + ' AND Device.[Deleted] = @xDeleted ORDER BY [DeviceName]'

SELECT @SqlParams = '@xDeleted bit '

EXEC sp_executesql @SqlStatement,@SqlParams,@IncludeDeleted

END

or, you could do it like this:

IF @IncludeDeleted = 0

BEGIN

SELECT @SqlStatement = @SqlStatement + ' AND Device.[Deleted] = 0 ORDER BY [DeviceName]'

--SELECT @SqlParams = '@xDeleted bit '

EXEC sp_executesql @SqlStatement,@SqlParams,@IncludeDeleted

END

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

meo2 Valued Member Points: 70 More actions · Answer 2

Wayne,

Thanks for the response. When I tried your first approach I get the following:

Msg 134, Level 15, State 1, Line 1

The variable name '@xDeleted' has already been declared. Variable names must be unique within a query batch or stored procedure.

(1 row(s) affected)

I am not in favor of the second method because of the Hard coding of the value eventhough it is either 0 or 1

Cheers,