Post reply

what are all the suitable conditions to use sq_executesql

  • July 11, 2005 at 3:22 am

    #165168

    Why do we use sp_executesql and what are the suitable conditions to use that statement

  • July 11, 2005 at 3:51 pm

    #573159

    You can output a value to a @Variable as one example... 

    (In this instance, I have created a #TempTable called #Variables and loop through it for each transaction.  I populate a variable called @CodeTBL and later string these @Variables together for a @SQL statement to execute).    May not be pretty, but it was a real booger to get working...  Steve at FarmersArms and went back and forth on this puppy...

    DECLARE @CodeTBL varchar(10)

     SET @SQL = N'SELECT @OutPut = Code' + CONVERT( varchar, @Counter) + 'TBL FROM #Variables'

     EXEC sp_executesql @SQL, N'@OutPut varchar(10) OUTPUT', @OutPut = @CodeTBL OUTPUT

    I wasn't born stupid - I had to study.

  • July 12, 2005 at 2:15 am

    #573258

    It's suitable in most cases where execution of dynamic sql is required.

    Directly from BOL

    "Using sp_executesql is recommended over using the EXECUTE statement to execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server."

    Dan
    www.firstcs.co.uk

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

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