dynamic sql and getdate function

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 and getdate function

Author

Topic

dark_cloud
Starting Member

2 Posts

Posted - 2006-05-05 : 07:13:38

hello,
i have a problem when i want to use dynamic sql to insert the current date in my property afsluitdatum(datetime) in table klacht

using sql server 2005

the sql statement looks like this, note that it is only partial

@sql nvarchar(1000),
@sqlt nvarchar(500),
@sqltv nvarchar(500),
@paramlist nvarchar(1000),

select @sql= 'INSERT into klacht'
select @sqlt ='(bedrijfId,Terecht,Ernstig'
select @sqltv ='VALUES(@xbedrijfid,@xterecht,@xernstig'

if @oorzaakKlacht not like '-1'
begin
select @sqlt=@sqlt +',OorzaakKlacht'
select @sqltv=@sqltv +',@xoorzaakKlacht'
end
if @afgesloten=1
begin
select @sqlt=@sqlt+',AfsluitDatum'
select @sqltv=@sqltv+',@xdatum'
end

select @sql=@sql+@sqlt+') '+@sqltv+')'

select @paramlist='@xdatum datetime,
@xOorzaakKlacht text'

EXEC sp_executesql @sql,@paramlist,@oorzaakKlacht,getdate

Error converting data type nvarchar to datetime

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-05 : 07:21:33

[code]EXEC sp_executesql @sql,@paramlist,@oorzaakKlacht,getdate()[/code]

dark_cloud
Starting Member

2 Posts

Posted - 2006-05-05 : 08:07:51

if i do that then i get a error "incorrect syntax near ')'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-05 : 08:30:45

sorry....


declare @date datetime
select @date = getdate()
EXEC sp_executesql @sql,@paramlist,@oorzaakKlacht, @date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources