Primarily there are two ways to execute a string or a variable in a string format as sql, they are using EXEC and SP_EXECUTESQL.
sp_executesql is always to be preferred for executing Dynamic SQL because, sp_executesql supports parameter substitution and also there are more chances of the reuse of execution plan when we use sp_executesql.
What we should remember when using sp_ExecuteSQL is that, it always executes as a separate batch irrespective if the batch/ procedure it is contained in.The transact SQL Contained in a variable which is being executed through the SP_ExecuteSQL has its Execution plan generated separately.
Here are a set of example on how a sp_Executesql can be used, and what are the basic mistakes that we commit while using sp_Executesql.
Using sp_ExecuteSQL, sp_executesql error – Data Type of Vairable
Here is a basic example of sp_ExecuteSQL, where we declare a variable and assign a valid sql to it and when executed it throws an error “Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar‘.”
This tells us that, it is compulsory to use variable of the above data types.
Use AdventureWorks GO DECLARE @SqlStr varchar(500) SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee' EXEC sp_executesql @SqlStr Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Using sp_ExecuteSQL, sp_executesql error - Parsed and Compiled
Now, lets make another mistake deliberately to see what happens if we use an invalid sql statement in the variable which is being executed through sp_ExecuteSQL, it tells us that the sql query in the variable is parsed and compiled only when executed through the sp_ExcuteSQL
Use AdventureWorks GO DECLARE @SqlStr nvarchar(500) SET @SqlStr='SELECT TOP 10 EmployeeID,A_NationalIDNumber,Title FROM HumanResources.Employee' EXEC sp_executesql @SqlStr Msg 207, Level 16, State 1, Line 1 Invalid column name 'A_NationalIDNumber'.
Using sp_ExecuteSQL,Correct Method
Now, lets go and put correct sql in the variable and run it using sp_ExecuteSQL to see how it works.
Use AdventureWorks GO DECLARE @SqlStr nvarchar(500) SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee' EXEC sp_executesql @SqlStr
Using sp_ExecuteSQL, Parameter Concatenation
The below code snippet shows how a string can be formed dynamically and then be assigned to a variable which is in turn executed through the sp_ExecuteSQL stored procedure
Use AdventureWorks GO DECLARE @Employeeid int DECLARE @SqlStr nvarchar(500) SET @EmployeeID=10 SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar) EXEC sp_executesql @SqlStr
Using sp_ExecuteSQL, How to Save the result set in a table
This example, shows how a result set that is being returned via sp_ExecuteSQL be stored in a temporary table.
USE AdventureWorks GO CREATE TABLE #usp_TestInsertWithSp_exec(EmployeeID int, NationalIDNumber nvarchar(15),Title nvarchar(50)) DECLARE @Employeeid int DECLARE @SqlStr nvarchar(500) SET @EmployeeID=10 SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar) INSERT INTO #usp_TestInsertWithSp_exec EXEC sp_executesql @SqlStr SELECT * FROM #usp_TestInsertWithSp_exec DROP TABLE #usp_TestInsertWithSp_exec
Using sp_ExecuteSQL, OUTPUT Parameters
The below code gives a basic example of How to specify output parameters when we use the sp_executesql stored procedure in SQL Server.
Use AdventureWorks GO DECLARE @Employeeid int DECLARE @NationalID nvarchar(15) DECLARE @SqlStr nvarchar(500) SET @EmployeeID=10 SET @SqlStr='SELECT @NationalID= NationalIDNumber FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar)+'' EXEC sp_executesql @SqlStr, N'@NationalID nvarchar(15) OUTPUT',@NationalID OUTPUT PRINT @NationalID
I suppose, that I have covered most of the points on SP_ExecuteSQL, if you have any comments regarding this sp_ExecuteSQL topic or any other topic on SQL Server, Please feel free to comment.
If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same.
{ 0 comments }
