dynamic sql syntax error - 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 syntax error

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-08-01 : 18:33:22

Hi friends
I need ur help with syntax of following dynamic sql.actually it is simple version of what am doing but i know for sure this line actually causing the error.

DECLARE @p_taskentrydtfilter nvarchar(50),
@taskentrydttag nvarchar(100)
declare @p_taskentrydt DATETIME

set @p_taskentrydtfilter = '>='
set @p_taskentrydt = '20050609'

select @taskentrydttag=case when (@p_taskentrydt='' OR @p_taskentrydt is null) then '' else
' and task.entrydt '+
@p_taskentrydtfilter+''+@p_taskentrydt+''' and task.entrydt <'''+dateadd(d,1,@p_taskentrydt)+'' end

as u can see am storing a CASE stmt in a variable but it gives me error saying
"Syntax error converting datetime from character string."
any ideas .Thanks

Cheers

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-01 : 20:50:53

>> as u can see am storing a CASE stmt in a variable
Nope - you're using a case statement to choose the data to populate a variable.

' and task.entrydt ' +
@p_taskentrydtfilter + ' ''' + convert(varchar(8),@p_taskentrydt,112) + ''' and task.entrydt < ''' + convert(varchar(8),dateadd(d,1,@p_taskentrydt),112) + '''' end

==========================================
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.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-08-01 : 21:14:34

>>Nope - you're using a case statement to choose the data to populate a variable.
yah sorry you r right.
BTW ur suggestion worked beautifully :)
Thank u very much

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources