SQL Server Dynamic SQL Execution

Question

I have a dynamic SQL query

DECLARE @ItemAreaCode NVARCHAR(MAX) = 'A062',
        @SQLStringDropTable NVARCHAR(MAX);

SET @SQLStringDropTable= 'DROP TABLE' + ' ' +
                         '[@ItemAreaCode].[ChangedRMAllocation]'

PRINT @ItemAreaCode

EXEC sp_executesql @SQLStringDropTable , N'@ItemAreaCode NVARCHAR(MAX)', @ItemAreaCode;

But when I execute this, I get this error:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '@ItemAreaCode.ChangedRMAllocation', because it does not exist or you do not have permission.

And the printed query is..

DROP TABLE [@ItemAreaCode].[ChangedRMAllocation];

What I need is:

DROP TABLE [A062].[ChangedRMAllocation];

PKirby · Accepted Answer · 2015-07-17 08:21:38Z

3

You are setting you parameter as a string within the dynamic SQL. @ItemAreaCode should not be included.

This should work :

DECLARE @ItemAreaCode NVARCHAR(MAX) = 'A062',
@SQLStringDropTable NVARCHAR(MAX);

    SET @SQLStringDropTable= 'DROP TABLE' + ' [' 
                            + @ItemAreaCode +  '].[ChangedRMAllocation]'

    PRINT @ItemAreaCode
    EXEC (@SQLStringDropTable);

Hope this helps

edited Jul 17, 2015 at 8:21

answered Jul 17, 2015 at 8:15

PKirby

8893 gold badges16 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andomar Over a year ago

Agree that the variable should be expanded in the string (although better with quotename), but why do you still pass it as a parameter to sp_executesql ?

tarzanbappa Over a year ago

@Andomar I need to write a query preventing sql injection here. That's why I used sp_executesql and pass parameters in to it. If this answer is not the right way how to do that

Andomar Over a year ago

@tarzanbappa: This answer is vulnerable to SQL injection through @ItemAreaCode = ']; drop database importantstuff; --' for example. You can't pass table names as parameters. Use quotename to avoid SQL injection.

Andomar · Accepted Answer · 2015-07-17 08:19:45Z

1

You probably meant to expand the variable:

DECLARE @ItemAreaCode sysname = 'A062',
    @SQLStringDropTable NVARCHAR(MAX);

SET @SQLStringDropTable = 
    'DROP TABLE ' + quotename(@ItemAreaCode) + '.[ChangedRMAllocation]';
EXEC (@SQLStringDropTable);

edited Jul 17, 2015 at 8:19

answered Jul 17, 2015 at 8:13

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

tarzanbappa Over a year ago

If the parameter is not a table name, Can I use a query like mine in the question?

Andomar Over a year ago

It's better to avoid dynamic SQL if you can. Parameterized queries are almost always preferred.

Shiju Shaji · Accepted Answer · 2015-07-17 08:20:47Z

1

Try this

DECLARE @ItemAreaCode NVARCHAR(MAX) = 'A062'
    ,@SQLStringDropTable NVARCHAR(MAX);

SET @SQLStringDropTable = 'DROP TABLE [' + @ItemAreaCode + '].[ChangedRMAllocation]'

EXEC (@SQLStringDropTable)

answered Jul 17, 2015 at 8:20

Shiju Shaji

1,73017 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL Server Dynamic SQL Execution

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related