Post reply

use exec or sp_executesql to build SQL dynamically?

  • May 16, 2004 at 5:43 am

    #85018

    Good morning,

    I want to build a SQL statment dynamicaaly through a nvarchar variable

    so what is the best .. to use exec or sp_executesql?

    I use it in a stored procedure .. so I want to capture if error happened (throug @@error global variable for example) .. so does exec or sp_executesql support @@error after it?

    thanks for anyone who help.


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • May 17, 2004 at 2:20 am

    #506601

    yes it does

    sp_executesql has more chance of using a cached execution plan whereas exec will not

    cheers

    dbgeezer

  • May 17, 2004 at 7:47 am

    #506644

    Another good thing to keep in mind is that if you are using the string to exec anything that has a sysname's or table names etc.. then make variable that holds the string a nvarchar, and using sp_executesql is the only way to go.

     


    Thanks, and don't forget to Chuckle

  • May 18, 2004 at 1:42 am

    #506740

    thanks everybody

    I use sp_executesql and if any error happens ..the @@error varialble will hold it.

    I also take in my mind that any variable you will use in sp_executesql it must be transrefed to nvarchar

    this is an example:

    declare @DailyInvID int

    declare @SqlStmt nvarchar(250)

    set @DailyInvID =1

    set @SqlStmt = 'INSERT INTO Inventory ( DailyInvID, Code )'

    set @SqlStmt = @SqlStmt + ' Values( ' + cast(@DailyInvID as nvarchar) +' , 100)'

    exec sp_executesql @SqlStmt

    if @@Error = 0

    commit transaction

    else

    Rollback transaction

    I hope this help u


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply