CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012dynamic-sql
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Using dynamic WHERE clause with sp_executesql

I can not get dynamic where clause working. The query I use:

IF NOT EXISTS ( SELECT  *
                FROM    sys.tables
                WHERE   name = 'a' )
    BEGIN
        CREATE TABLE a ( a INT );

    END;
DECLARE @whereClause NVARCHAR(MAX) = ' 1=1 ';

DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM a WHERE @whereClause';

EXEC sp_executesql @sql, N'@whereClause NVARCHAR(MAX)', @whereClause;

DROP TABLE a;

Then additional question would be: is there any possibility to debug query that is executed with sl_executesql?

Upvotes: 0

Views: 1767

Answers (1)

BeaglesEnd
BeaglesEnd

Reputation: 421

As as been stated you can't use the parameters on sp_executesql to replace statement objects, only parameter variables.

If you need to build the WHERE clause dynamically, I find it easier to use REPLACE for the Object components of the statement.

DECLARE @whereClause NVARCHAR(MAX) = ' 1=@whereVariable ';
DECLARE @whereVariable INT = 1;
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM a WHERE @whereClause';

SELECT @sql = REPLACE(@sql, '@whereClause', @whereClause)

EXEC sp_executesql @sql
    ,'@whereVariable INT'
    ,@whereVariable = @whereVariable;

This means that the statement can be built without interlived + and variables. It then means that the input and output parameters are used as normal.

Upvotes: 2

Related Questions