sql server 2008 - Adding parameters to a dynamic sql query -

my sql procedure

 set @query='update '+@tbl+' set  auth_date='''+@authdate+'''     ,authorized_by_id=@authid     ,authorized=1 '+@col+'=@id;     if(@@error=0) set @reval=1 else set @reval=-1'        execute sp_executesql @query, n'@reval int output',      @reval,n'@authid int',@authid,n'@id int',@id 

here @authid datetime, @authid , @id int

1) how can pass arguments . 2) there error coming conversion failed when converting date and/or time character string.

you pass argument definition in second parameter, followed arguments, in order declared:

execute sp_executesql @query,      n'@authid int, @id int, @reval int output',      @authid, @id, @reval output; 

any reason why don't pass @authdate parameter? specially since datetime types notoriously error prone when converted strings, due locale settings.

last, should use try/catch blocks instead of @@error checks.