SEO

#1 (**permalink**) 02-28-2008, 08:03 PM

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.

#2 (**permalink**) 02-28-2008, 08:03 PM

justin.wong@iomer.com (Justin Wong) wrote in message news:<76c6d915.0402192301.6d1c901c@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)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

'Syntax error converting datetime from character string' with sp_executesql