0

I'm using EXEC sp_executesql for a dynamic query in SQL Server 2017.

I've tried various testing scenarios, and I can get results in my query (for other parameters) as long as the values passed in are Integers. So, that means, Location and Department testing works. However, I can't figure out if there's something I need to do differently for when I'm sending a NVARCHAR or DateTime.

Here's my stored procedure, with the NVARCHAR param. Do you see anything I'm doing wrong?


(
@tktitle NVARCHAR(200)
)

AS
BEGIN

Declare  @SQL NVARCHAR(MAX)

Set @SQL = 'SELECT        timekeep.tkinit, timekeep.tkfirst, timekeep.tklast, 
                         timekeep.tkemdate, timekeep.tktitle, timekeep.tkloc, timekeep.tkdept

FROM   abc.xyz'        
                     


IF @tktitle IS NOT NULL
Select @SQL = @SQL + 'AND ([tktitle] = @tktitle)'
EXEC sp_executesql @SQL, N'@tktitle varchar',  @tktitle


END
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Susan T.
  • 21
  • 1
  • 8

1 Answers1

0

I can identify at least three issues:

  • You need to specify a length for varchar when passing it as a parameter.
  • You also need a space before the AND and the AND should be a WHERE.
  • You need to assign the parameter in the execute call.

So:

IF @tktitle IS NOT NULL
    Select @SQL = @SQL + ' WHERE ([tktitle] = @tktitle)';
-------------------------^ separator
EXEC sp_executesql @SQL, N'@tktitle varchar(200)', @tktitle=@tktitle;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • And the SQL stub needs a where clause. Right now is just `FROM abc AND tktitle..` – Caius Jard Oct 23 '20 at 19:11
  • Tada! Adding the length on the parameter fixed it! (I had the where in there, but took it out in my query to streamline my post). Thank you both for such quick responses. Should have posted an hour ago. :-) – Susan T. Oct 23 '20 at 19:15