July 11, 2005 at 3:22 am
Why do we use sp_executesql and what are the suitable conditions to use that statement
July 11, 2005 at 3:51 pm
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
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."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply