Adding parameters to a dynamic sql query

Question

My sql procedure is

 SET @query='UPDATE '+@tbl+' SET  auth_Date='''+@authDate+'''
    ,authorized_By_ID=@AuthID
    ,authorized=1 WHERE '+@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 is Datetime, @AuthID and @Id is int

1) How can I pass arguments . 2) There is an error is coming Conversion failed when converting date and/or time from character string.

Remus Rusanu · Accepted Answer · 2011-02-19 05:11:43Z

5

You pass argument definition in the second parameter, followed by the arguments, in the order declared:

EXECUTE sp_executesql @query, 
    N'@AuthID int, @Id int, @reVal int OUTPUT', 
    @AuthID, @Id, @reVal OUTPUT;

Any reason why you don't pass @authDate also as a parameter? specially since datetime types are notoriously error prone when converted to strings, due to locale settings.

Last, you should use TRY/CATCH blocks instead of @@ERROR checks.

answered Feb 19, 2011 at 5:11

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding parameters to a dynamic sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related