Microsoft SQL Server: Prepared Statements

Question

How do you create and use a prepared statement in SQL Server?

In PostgreSQL, you do something like:

PREPARE prepared (varchar, varchar, etc) AS
    INSERT INTO table(field,field,etc)
    VALUES ($1,$2,etc);
EXECUTE (value,value,etc);

Something similar in MySQL.

I can’t find any examples in SQL Server. I have seen a number of examples using a function, but I don’t think that’s the same thing.

Thanks

Answer 1

Prepared statements are supported in SQL Server, but you are probably having a hard time finding examples of using them because they aren't really necessary.

You could call sp_prepare to get a handle and then pass that to sp_execute. Or you can call either sp_prepexec or sp_prepexecrpc, both of which combine the "prepare" and "exec" steps. In all cases, you would end by calling (eventually) sp_unprepare, passing in the handle returned by sp_prepare, sp_prepexec, and sp_prepexecrpc.

However, as stated previously, this pattern is obsolete / unnecessary in Microsoft SQL Server as queries and execution plans are naturally cached. Queries are cached, and looked up, based on a hash of the query text. Ad hoc queries are cached based on the exact, full text of the query (binary comparison), including values concatenated into the query to use for filtering, etc. Those plans are less likely to get re-used, unless the exact same query (with respect to white-space and everything else) is executed again. Parameterized queries are cached based on the query text which does not include the parameter values, and can be re-used for different values of the parameters -- just like when doing a "prepare". Hence, no need to call prepare / exec / unprepare.

If you execute queries using Stored Procedures, sp_executesql (for Dynamic SQL), or a properly parameterized query from app code, then SQL Server is already doing a lookup to see if that parameterized query has already been parsed and compiled (i.e. "prepared") and if so, then it will use the cached info.

I believe that the most wide-spread usage of this pattern in SQL Server is by ODBC drivers. When writing an application that can connect natively to SQL Server, one would not employ this pattern. And in fact, I would recommend against using this pattern (whenever possible) as it has the downside of taking up more memory per each Session: it stores the query definitions so that the execution plan can be regenerated in cases where the plan was, for whatever reason, removed from the plan cache. For more details on this behavior, including sample .NET / C# code, please see my other answer here on DBA.StackExchange:

What is the sense and benefit of using SqlCommand.Prepare()?

Answer 2

example:

declare @p1 int
exec sp_prepare @p1 output,N'@P0 int',N'
select * from sys.tables where object_id>@P0'
exec sp_execute @P1,'0'
EXEC sp_unprepare @P1;