Dynamic Sp_Executesql failing on datetime conversion error

Question

I have a very simple dynamic SQL query that specifically needs to be called using sp_executesql with parameters. This query works fine in regular dynamic SQL, but fails when using sp_executesql on a conversion error.

I have tried many combinations of dynamic SQL, but none of them seem to work specifically for datetime conversions related to sp_executesql.

declare 
@sql_nvarchar nvarchar(max),
@datetime datetime = GETDATE(),
@sqlparams nvarchar(max),
@tablename nvarchar(max) = 'SomeTableName'

Set @sql_nvarchar =
N'
Select *
from ' + @tablename + '
where Date > ''' + convert(nvarchar(23), @datetime, 101) + ''' '

Set @sqlparams =
N'
@datetime datetime,
@tablename nvarchar(max)
'

EXEC(@sql_nvarchar)

EXEC [sp_executesql] @sql_nvarchar,@sqlparams, @datetime, @tablename

The first exec correctly returns the desired query, the second EXEC throws an error: 'Error converting data type nvarchar(max) to datetime.'

Your query works when I run it. Are you sure the Date column of SomeTableName is of type datetime? — Dale K
@DaleBurrell, if I add a temp table #SomeTableName, both EXECs complete without error, but I still see the error message when I replace #SomeTableName with a production table that has a datetime field with the correct field name referenced. — skewed_to_death_94
@DaleBurrell How do I get the parameters to be passed in? I don't want to use a static query for testing because the whole issue is that I can get it to work in regular and dynamic sql, just not sp_executesql. — skewed_to_death_94
Why are you converting @datetime to a varchar to compare it with a date for anyway? Its already a date, so you don't need to convert it to anything. Maybe thats the issue? Remove the convert. — Dale K

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-06-12T20:28:20

You cannot parameterize an identifier, such as a table name. So, phrase this as:

Set @sql_nvarchar = N'
Select *
from ' + @tablename + '
where Date > @datetime
';

Set @sqlparams = N'@datetime datetime'
exec sp_executesql @sql_nvarchar, @sqlparams,
                   @datetime=@datetime

Dynamic Sp_Executesql failing on datetime conversion error

1 Answers