Needless to say, my posts here will reflect all the new things I'm learning about T-SQL and SQL Server in general beyond the usual reports-driven information I have acquired in my work. And this week I learned a brand new technique for handling queries: "preparing" them.
In the world of T-SQL the word "prepare" has a very specific definition, which is something like this: "load a query statement into memory and assign it a handle (ID) so that it can be executed repeatedly by means of the handle." Typically "prepared" queries are also "parameterized," meaning that the query requires one or more parameters to be defined with values before the query can return results.
There are four stored procedures to handle "prepared" queries, and the rest of this entry will explain each one.
sp_prepare is the command to use in order to "prepare" a query statement and define one or more parameters for it. It accepts three parameters itself.
The first parameter is an reference to a variable that has already been defined as an integer in an earlier DECLARE statement. This parameter must also be defined as an OUTPUT parameter, meaning that the stored procedure will assign the integer value to the variable.
The second parameter is an nvarchar value of one or more variable definitions. The string value for this parameter should be formatted just like a DECLARE statement but without the word DECLARE.
The third parameter is also an nvarchar value and contains the query to be prepared. Here is an example of using the sp_prepare stored procedure to prepare a query that pulls the members of a family from Shelby.NANames based on a provided FamNu value.
declare @FamilyMembers int;
exec sp_prepare @FamilyMembers output, N'@FamilyNumber int', N'select NameCounter, FirstMiddle, LastName, Birthdate from Shelby.NANames where FamNu = @FamilyNumber';The @FamilyMembers variable will contain the integer assigned by the sp_prepare procedure as the "handle" for the prepared query. From this point in the SQL script, I can reference @FamilyMembers to execute the prepared query instead of typing it all out again. There is a special stored procedure for executing prepared queries, and that is discussed next.
sp_execute is the command to execute prepared queries. It accepts two parameters. The first parameter is a reference to the handle of the prepared query. Normally this takes the form of the variable used to define the prepared query in the sp_prepare procedure. The second parameter is a varchar list of the value(s) to be used for the parameter(s) in the prepared query. The following two lines of code will execute the prepared query from the example code above for two different families.
exec sp_execute @FamilyMembers, '2';exec sp_execute @FamilyMembers, '5';As you can see, after the query has been prepared, it is very easy to execute the query over and over with various parameter values. These two commands (sp_prepare and sp_execute) are so commonly found with the second immediately following the first that Microsoft added a stored procedure to do them both together.
sp_prepexec prepares a query and immediately executes it. It has the same three parameters as sp_prepare but adds a fourth parameter for the initial set of variable values for the execution of the prepared query. The following code shows how the same family member query can be prepared and executed all at once.
exec sp_prepexec @FamilyMembers output, N'@FamilyNumber int', N'select NameCounter, FirstMiddle, LastName, Birthdate from Shelby.NANames where FamNu = @FamilyNumber', '7';With the advent of sp_prepexec, the sp_prepare stored procedure is hardly ever used. But sp_execute remains as the only way to re-execute a prepared query after the first time. And if you don't plan to execute the query more than once, "preparing" the query is not really worth the effort.
sp_unprepare is the final stored procedure for dealing with prepared queries. As its name states, this one removes a prepared query from memory. It has only one parameter, which is the handle of the prepared query to unprepare. As with sp_execute, this is normally the variable that was defined to capture the handle from the sp_prepare or sp_prepexec stored procedure. The final example is how to "clean up" the memory after the family members prepared query is no longer needed.
exec sp_unprepare @FamilyMembers;And in case you want to run the whole script all together in SQL Server Management Studio, here is the complete script to parepre, execute, and remove the sample prepared query:
declare @FamilyMembers int;
exec sp_prepexec @FamilyMembers output, N'@FamilyNumber int', N'select NameCounter, FirstMiddle, LastName, Birthdate from Shelby.NANames where FamNu = @FamilyNumber', '7';
exec sp_execute @FamilyMembers, '2';
exec sp_execute @FamilyMembers, '5';
exec sp_unprepare @FamilyMembers;
Although prepared queries are not something I can imagine using for report designs, they would be helpful for applications that need to load the same columns of information over and over from the same table(s) with just one or two variables to define each time a user of the application loads. Of course, a permanently defined stored procedure would do just as well, if not slightly better. But if you can't define a permanent stored procedure, "preparing" a frequently-used query is the way to go.
