dynamic sql sp_executesql works but shoots blanks

Question

Having some fun with t-sql and sp_executesql (test output below). The last piece of the puzzle was the need for sp_executesql to have uppercase N for the query and definition parameters.

I have a feeling that the input parameter @cardcodeParm usage is the problem.

As you can see the first two shots work. The third works but nothing is returned.

5> execute sp_executesql N'select cardcode, cardname from dbo.ocrd where @dummy = @dummy  and cardcode = ''BTG000'' ', N' @dummy int ', @dummy = 1
6> go
cardcode        cardname
--------------- ----------------------------------------
BTG000          HUGRO GmbH

(1 rows affected)
1> execute sp_executesql N'select cardcode, cardname from dbo.ocrd where @dummy = @dummy  and cardcode = ''BTG000'' ', N' @dummy nvarchar(5) ', @dummy = '1'
2> go
cardcode        cardname
--------------- ----------------------------------------
BTG000          HUGRO GmbH

(1 rows affected)
1> execute sp_executesql N'select cardcode, cardname from dbo.ocrd where cardcode = ''@cardcodeParm'' ',
2>     N' @cardcodeParm nvarchar(100) ', @cardcodeParm = 'BTG000';
3> go
cardcode        cardname
--------------- ----------------------------------------

I am on the learning curve with t-sql using sql server 2005 with sqlcmd client access.

Answer 1

You won't need ' around your variable, so you can omit them, and your line will look like this:

execute sp_executesql N'SELECT cardcode, cardname
                        FROM dbo.ocrd
                        WHERE cardcode = @cardcodeParm ',
N' @cardcodeParm nvarchar(100) ', @cardcodeParm = 'BTG000';

It will be exactly the same as when you did not have the ' marks around your @dummy variable in the first two successful queries.