dynamic sql query syntax

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

Answer this question

dynamic sql query syntax

Sami Karaeen

Hi,

I think its your date format. try setting your @p_taskentrydt to other date date formats such as mm/dd/yyyy

cheers,

Paul June A. Domag
shitalkochat
@p_taskentrydtfilter+''+@p_taskentrydt + '''

should be:

@p_taskentrydtfilter+''''+@p_taskentrydt + '''

But you should be using sp_executesql instead of dynamically concatenating variables that can be set by caller for example. See BOL for more details on how to use sp_executesql to execute parameterized SQL statements.
bojanplatisa
Thanks Umachandar
That worked nicely
dynamic sql query syntax

Answer this question