Using sp_executesql with nvarchar parameters: incorrect syntax near '='

Question

Post reply

Using sp_executesql with nvarchar parameters: incorrect syntax near '='

ktrammen

SSC Veteran

Points: 226
More actions
May 17, 2018 at 10:34 am

#334822

I've been trying to get this right for well over an hour and I'm now mentally drained.
I have a procedure that takes various parameters, but it falls over on the ELSE clause where I have "SET  @Where  = 'WHERE Name = @ItemNameInput';"
Can someone else have a look at what the issue is? Partial T-SQL as follows...
DECLARE @sql   NVARCHAR(4000); DECLARE @InsertInto  NVARCHAR(4000); DECLARE @Select   NVARCHAR(4000); DECLARE @Where   NVARCHAR(4000);

SET  @InsertInto  = ' INSERT INTO SearchResults (SearchResultsSetID, ItemID)';
SET  @Select   = ' SELECT @SearchResultsSetID, ID FROM Items';
IF @ItemNameInput = ''
  SET  @Where  = ' WHERE Name LIKE %';
ELSE
  SET  @Where  = 'WHERE Name = @ItemNameInput';
SET @SQL    = @InsertInto + @Select + @Where;
EXEC sp_executesql @sql, N'@SearchResultsSetID INT', @SearchResultsSetID, '@ItemNameInput NVARCHAR(100)', @ItemNameInput;
(Sorry, no idea why formatting doesn't work for me.)
May 22, 2018 at 7:52 am

This was removed by the editor as SPAM
May 22, 2018 at 7:58 am

This was removed by the editor as SPAM
May 22, 2018 at 7:59 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

replace
SET @Where = ' WHERE Name LIKE %';
with
SET @Where = ' WHERE Name LIKE ''%''';

ktrammen SSC Veteran Points: 226 More actions · Answer 2

Joe, yes, that will of course be another issue when the issue with "WHERE Name = @ItemNameInput" is resolved -- thanks for catching that.

Any ideas about the "WHERE Name = @ItemNameInput" issue?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

ktrammen - Thursday, May 17, 2018 10:42 AM
Joe, yes, that will of course be another issue when the issue with "WHERE Name = @ItemNameInput" is resolved -- thanks for catching that.
Any ideas about the "WHERE Name = @ItemNameInput" issue?

You're missing the space before the WHERE (or after Items)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99130 More actions · Answer 4

PRINT @sql;
Your best friend when debugging dynamic SQL. Debug the SQL output from the PRINT, and then propagate any fixes the the SQL that generated your dynamic SQL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

This is how I would write your code that you posted. I understand that you are putting pieces together, but I think this helps illustrate what you are attempting to accomplish a little easier. Also, your EXEC sp_executesql is not correct and if it was working, I'm not sure how or why.

-- your exec: EXEC sp_executesql @sql, N'@SearchResultsSetID INT', @SearchResultsSetID, '@ItemNameInput NVARCHAR(100)', @ItemNameInput; DECLARE @SQLCmd NVARCHAR(MAX) , @SQLParm NVARCHAR(MAX);

SET @SQLParm = N'@SearchResultsSetID INT, @ItemNameInput NVARCHAR(100)';
SET @SQLCmd = N'
INSERT INTO SearchResults (
SearchResultsSetID
, ItemID)
SELECT
@SearchResultsSetID
, ID
FROM
Items
WHERE
Name LIKE CASE WHEN @ItemNameInput = ''''
                 THEN ''%''
                 ELSE @ItemNameInput -- as long as there is no wild card in the input value this works like an =
            END;';

PRINT @SQLCmd;

EXEC [sys].[sp_executesql] @stmt = @SQLCmd
                         , @params = @SQLParm
                         , @SearchResultsSetID = @SearchResultsSetID
                         , @ItemNameInput = @ItemNameInput;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Thom A - Thursday, May 17, 2018 10:55 AM
PRINT @sql;
Your best friend when debugging dynamic SQL. Debug the SQL output from the PRINT, and then propagate any fixes the the SQL that generated your dynamic SQL.

Although I used PRINT in the code I posted I actually use the following to look at the dynamic SQL I normally write as sometimes (usually) longer than can be fully output by PRINT in SSMS:
if @pDebug = 1 select cast('<![CDATA[' + @SQLCmd + ']]>' as xml);

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Changed my code some:
-- your exec: EXEC sp_executesql @sql, N'@SearchResultsSetID INT', @SearchResultsSetID, '@ItemNameInput NVARCHAR(100)', @ItemNameInput; DECLARE @SQLCmd NVARCHAR(MAX) , @SQLParm NVARCHAR(MAX) , @ItemNameInput NVARCHAR(100) = '';

SET @SQLParm = N'@SearchResultsSetID INT, @ItemNameInput NVARCHAR(100)';
SET @SQLCmd = N'
INSERT INTO SearchResults (
SearchResultsSetID
, ItemID)
SELECT
@SearchResultsSetID
, ID
FROM
Items
WHERE
Name' + CASE WHEN @ItemNameInput = '' THEN N' LIKE ''%''' ELSE N' = @ItemNameInput;' END;

PRINT @SQLCmd;
select cast('<![CDATA[' + @SQLCmd + ']]>' as xml);

EXEC [sys].[sp_executesql] @stmt = @SQLCmd
                         , @params = @SQLParm
                         , @SearchResultsSetID = @SearchResultsSetID
                         , @ItemNameInput = @ItemNameInput;