Tuesday 29 January 2008

Passing datetime parameter to SQL Stored procedure in Query Analyzer

Yesterday I faced problem passing datatime parameter in sql server queryanalyzer, where I was trying to run a procedure UP_ORDER_LOG which expects parameter @co_id, @Prog_id, @event and @event_date

So I tried executing the procedure like

exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=getdate()

exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=getdate

exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=CURRENT_TIMESTAMP

But everytime I got the error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

So finally I cracked the above mentioned problem with the solution given below :

declare @eventdate datetime
select @passdate = CURRENT_TIMESTAMP
exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=@eventdate

I hope this will help.
Please do add comments if you get some other solution also.
Share:

0 comments:

Post a Comment