ju*********@iomer.com (Justin Wong) wrote in message news:<76**************************@posting.google. com>...
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.
Any help would be greatly appreciated.
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)) + ''''
EXEC (@sql)
