Today I'm going to slip in a quick post before the month ends. Today we are going to look at sp_executesql.
This system stored procedure allows users to run batches of T-SQL code. Its use of parameters allowed for a bit more security as opposed to using something like EXEC by itself. Below we can see a simple example of the format needed to use sp_executesql.
--------------------------------------------------------------------------------------
DECLARE @SQLString NVARCHAR(500);
SET @SQLString = N'SELECT @MessageOut = f.message
FROM Test.dbo.Files f
WHERE File_ID = @level';
DECLARE @ParmDefinition NVARCHAR(500);
SET @ParmDefinition = N'@level tinyint,
@MessageOut varchar(30) OUTPUT';
DECLARE @Result VARCHAR(30);
EXEC sp_executesql
@SQLString,
@ParmDefinition,
@level = 2,
@MessageOut = @Result OUTPUT;
SELECT @Result;
--------------------------------------------------------------------------------------
To use sp_executesql we can to do the following:
- Declare a SQLString variable to hold the T-SQL query/batch we would like to run.
- Declare a Parameter Definition string to hold a string of parameters that we would like to use. We can also declare a OUTPUT parameter here in order to pass information back to the caller.
- If you are returning information you may want to declare a variable to store the returned information (@Result).
- Use the sp_executesql system stored procedure and give it the SQL String variable as well as the parameter definition and the values for each parameter defined. If you have an OUTPUT parameter you can save the result to your @Result variable.
- Finally, select your @Result if you have one.
And there you go. You can help prevent SQL Injection in your applications this way. :-)
EDIT: Also, and I may go into this in more detail later, but I saw a very good demonstration illustrating that sp_executesql caches execution plans even if you use different parameter values. This can help speed up queries a good bit. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
