I am trying to run a stored procedure in SQL Server 14 and I need to concatenate a string and execute it. In that string, I want to do a select of a column if the parameter is null. But for some reason, using ISNULL with a concatenated string doesn't return anything except for 'Commands completed successfully'.
I have created a simple scenario:
declare @startdate datetime = null;
declare @sql nvarchar(1000);
set @sql = '
select isnull('''+convert(varchar,@startdate,101)+''',''Is null'')'
exec(@sql)
and when I run that I would like to see the 'Is null' result, but I only see the statement: 'Command completed successfully' and the current time (completion time).
Can someone please tell me why this doesn't work the way that I expect it to?
The value of @startdate is null. When you convert a null datetime value to a varchar, you get (surprise) null.
We now take that null varchar and concatenate it with the rest of the literal varchar value to assign to @sql. Guess what: concatenating null with another string also produces null.
The value of the entire @sql for this exec() statement will be null!
Much better practice here is to use sq_executesql and include your extra data as it's own variable.