Dynamic SQL problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL problem

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-29 : 10:48:26

Hi,

declare @t_name varchar(100)
declare @name varchar(100)
declare @SQL varchar(1000)
declare @SQL_INS varchar(1000)
declare @maxdate smalldatetime

SELECT @SQL = 'select''@maxdate'' = MAX(fTime) from [Raw].dbo.[AUD/CAD]'
EXEC (@SQL)

*** here @maxdate gets the correct datetime value

SELECT @SQL_INS = 'select [fTime] from [Raw].dbo.[AUD/CAD] where [fTime] > ''@maxdate'''
EXEC (@SQL_INS)

*** here gives error converting character string to smalldatetime

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-29 : 11:50:13

I tried it and it doesn't make any sense indeed. Did You figure it out ?

robert

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-29 : 11:56:31

Look at the string you are executing
something like

select [fTime] from [Raw].dbo.[AUD/CAD] where [fTime] > '@maxdate'
fTime is a smalldatetime so it tries to convert the character string '@maxdate' to a smalldatetime and gets an error.

You probably want something like
declare @t_name varchar(100)
declare @name varchar(100)
declare @SQL nvarchar(1000)
declare @SQL_INS varchar(1000)
declare @maxdate smalldatetime
SELECT @SQL = 'select @maxdate = MAX(fTime) from [Raw].dbo.[AUD/CAD]'
EXEC sp_executesql @SQL, N'@maxdate smalldatetime out', @maxdate out

SELECT @SQL_INS = 'select [fTime] from [Raw].dbo.[AUD/CAD] where [fTime] > ''' + convert(varchar(22), @maxdate, 126) + ''''
EXEC (@SQL_INS)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-29 : 12:02:24

But @maxdate is declared as a smalldatetime so a conversion should not be necesseary.

robert

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-29 : 12:05:59

thanks nr, thats great

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-29 : 12:58:57

quote:
Originally posted by eurob

But @maxdate is declared as a smalldatetime so a conversion should not be necesseary.
robert

It's defined as a smalldatetime as a variable but
SELECT @SQL_INS = 'select [fTime] from [Raw].dbo.[AUD/CAD] where [fTime] > ''@maxdate'''

is not passing the contents of the variable it's passing the string '@maxdate' which of course is not convertible to a date.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-29 : 13:17:17

Thanks.

robert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources