CODEWITHSUNDEEP
t-sqlvariablesstored-proceduressql-server-2008-r2
MoonKnight
MoonKnight

Reputation: 23831

Subtleties of SQL Server Variables

I have the following SQL Server stored procedure:

CREATE PROCEDURE ispsDcOhcAgg @TmpTableName NVARCHAR(50), @ListItem NVARCHAR(50) 
AS
IF EXISTS (SELECT name 
           FROM sys.tables 
           WHERE name = @TmpTableName) 
DROP TABLE @TmpTableName; -- This will not work.
GO

This will clearly not work (see the comment in the above snippit). The only (and very ugly) way I have found to get around this problem is to do the following

CREATE PROCEDURE ispsDcOhcAgg @TmpTableName NVARCHAR(50), @ListItem NVARCHAR(50) 
AS
DECLARE @SQL NVARCHAR(4000) 
SET @SQL = N'IF EXISTS (SELECT name ' +
           N'FROM sys.tables ' + 
           N'WHERE name = N' + N'''' + @TmpTableName + N''') ' + 
           N'DROP TABLE ' + @TmpTableName + N';'
EXEC sp_executesql @SQL;
GO

which truly stinks and for large stored procedures, it's horrendous!

Is there another way of doing this that I don't know about?

Thanks for your time.

Upvotes: 3

Views: 463

Answers (2)

Treb
Treb

Reputation: 20299

No, if you want to determine the table to be dropped at runtime, there is no alternative to dynamic SQL.

There is a slightly less ugly way: you only use dynamic SQL for the command that needs to be dynamic (the DROP command):

DECLARE @SQL NVARCHAR(100) 
IF EXISTS (SELECT name 
       FROM sys.tables 
       WHERE name = @TmpTableName) 
BEGIN
    SET @SQL = N'DROP TABLE ' + @TmpTableName + N';'
    EXEC sp_executesql @SQL;
END

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147354

No, if you want to use a table name dynamically like this, you need to use dynamic SQL.

So you should make sure you don't open yourself up to nasty SQL injection risks!

Try something like this:

SET @SQL = 'IF EXISTS (SELECT name ' +
           N'FROM sys.tables ' + 
           N'WHERE name = @TableName) ' + 
           N'DROP TABLE ' + QUOTENAME(@TmpTableName) + ';'

EXEC sp_executesql @SQL, N'@TableName sysname', @TmpTableName;

Upvotes: 6

Related Questions