Using SP_EXECUTESQL

What we can do with EXECUTE?

With EXECUTE you can build the complicate query which contains the replacement of parameters values run time. Just imagine the situation where you have only single query which needs to get run 3-4 times; each time the substitution is taking place?

Have a look at the following query; which requires running twice and also substituting the values.

/* Following by using EXEC*/

DECLARE @AcTypeID nvarchar(40)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Gender int

/* Specify the parameter value*/
SET @AcTypeID = 1
set @Gender = 1

/* Build the SQL string*/
SET @SQLString = 'SELECT count(*) as TotalUserByAccount FROM [User] WHERE AccountTypeId = ' + CAST( @AcTypeID as NVARCHAR(10))
SET @SQLString = @SQLString + ' And Gender = ' + CAST(@Gender as NVARCHAR(1))

/* Execute the same string*/
EXEC(@SQLString)

/* Specify the parameter value*/
SET @AcTypeID = 5
set @Gender = 0

/* Build the SQL string AGAIN*/
SET @SQLString = 'SELECT count(*) as TotalUserByAccount FROM [User] WHERE AccountTypeId = ' + CAST( @AcTypeID as NVARCHAR(10))
SET @SQLString = @SQLString + ' And Gender = ' + CAST(@Gender as NVARCHAR(1))

/* Execute the same string*/
EXEC(@SQLString)

So this is the first problem with EXECUTE?command, now next problem; it does not generate execution plans which are more likely to be reused by SQL Server. So the performance is not good if we have such query execute frequent.

Now, using SP_EXECUTESQL we can overcome both of above mentions problem. SP_EXECUTESQL gives you the possibility to use parameterized statements, EXECUTE does not. Parameterized statements gives no risk to SQL injection and also gives advantage of cached query plan. I will show you the cached query plan too.

First here is the query.

/* Now lets use sp_executesql */

DECLARE @AcTypeID nvarchar(40)
DECLARE @Gender nvarchar(40)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(1000)

/* Build the SQL string once. */
SET @SQLString = N'SELECT count(*) as TotalUserByAccount FROM [User] WHERE AccountTypeId = @paramAcTypeID and Gender = @paramGender'

/* Specify the parameter format once. */
SET @ParmDefinition = N'@paramAcTypeID bigint, @paramGender int'

/* Set the param value */
set @Gender = 1
SET @AcTypeID = 1

/* Execute the query */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @paramAcTypeID = @AcTypeID, @paramGender = @Gender

/* set only param value again*/
set @Gender = 0
SET @AcTypeID = 5

/* Execute the query */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @paramAcTypeID = @AcTypeID, @paramGender = @Gender

Now let’s check our Cache Objects of SQL Server, [I used DBCC FREEPROCCACHE first so its cleare all the cache plan and run the query]



Now the thing that I like most; getting OUTPUT variable by using SP_EXECUTESQL, here are the code for getting variable as OUTPUT.

/* Variable declaration */
DECLARE @UserID uniqueidentifier
DECLARE @UserName nvarchar(40)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(1000)

/* Build the SQL string*/
SET @SQLString = N'SELECT @paramUserID = UserID FROM [User] WHERE FavUserName = @paramUserName'

/* Specify the parameter format once. */
set @ParmDefinition = N'@paramUserName nvarchar(40), @paramUserID uniqueidentifier output'

/* Execute the string with the parameter value. */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
            @paramUserName = 'imran786', @paramUserID = @UserID OUTPUT

/* Get the output value */
print @UserID

One of the limitations of SP_EXECUTESQL in SQL Server 2000 was that the input code string was practically limited to 4000 characters. This limitation is not relevant anymore because you can now provide sp_executesql with an NVARCHAR(MAX) value as input. Note that SP_EXECUTESQL supports only Unicode input—unlike EXEC which supports both regular character and Unicode input.

Read more...