CREATE PROCEDURE dbo.Synchronization_GetNewRecords
(
@item varchar(50),
@last datetime
)

AS

SET NOCOUNT ON

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @last

EXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @last


This is my SP. Very simple. But it is throwing the error in the subject line.

[quoted text, click to view]

There are a couple of issues here - you seem to be mixing the syntax
for EXEC() and sp_executesql; the error is because datetime has a
higher precedence than nvarchar, so the string is implicitly converted
to a datetime, which won't work. You need to explicitly cast or
convert the datetime.

In fact, in this case you can't use sp_executesql anyway, because it
won't accept a variable in place of the table name. You could use
EXEC() (see code below), but you probably shouldn't:

http://www.sommarskog.se/dynamic_sql.html#Dyn_table

Finally, if you do use this approach, you will need to use a safe
format for the datetime parameter, or you may get the same error
again, eg.:

'20040220' -- works everywhere
'20/02/2004' -- fails with US English

Simon


CREATE PROCEDURE dbo.Synchronization_GetNewRecords
@item varchar(50),
@last datetime

AS

SET NOCOUNT ON

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated > ''' +
cast(@last as nvarchar(50)) + ''''