sp_executesql syntax error

Question

I am trying to learn and use the sp_executesql and I am stuck even with this simple SQL statement.

DECLARE @sql NVARCHAR(MAX) = NULL
DECLARE @WHERE NVARCHAR(MAX) = NULL

SET @sql = N'
    SELECT
        *
    FROM
        MyTable
        @WHERE'

SET @WHERE = ISNULL(' WHERE ' + @WHERE, '');

EXECUTE sp_executesql 
    @statement = @sql
    , @params = N'@WHERE nvarchar(MAX)'
    , @WHERE = @WHERE;

I am getting this error and couldn't figure out why...

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '@WHERE'.

Answer 1

You can't have a where clause as a parameter.

I recommend you read this section of MVP Erland Sommarskog's article on the subject, and the whole article in general.

You could make this work without a parameter, even if it's not a good idea, by appending to your SQL string directly:

DECLARE @sql NVARCHAR(MAX) = NULL
DECLARE @WHERE NVARCHAR(MAX) = NULL

SET @sql = N'SELECT * FROM MyTable' + ISNULL(' WHERE ' + @WHERE, '');

EXECUTE sp_executesql @sql