5

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

10

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()?

2
  • There quite a lot of incorrect/unfinished statements here. SQL server plans are cached up to a certain quantity. If it's a prep stmt, then the cache is used efficiently. And it's absolutely necessary to run prep stmt in production to improve on SQL injection protection. Any raw sql uses a query stub and contributes to memory occupancy for SQL server. Prep stmt can utilize plan cache, but that also means it's hitting the DB twice as frequntly as the same raw SQL (if that's acceptable, which may very well be). – hagubear Feb 15 '19 at 15:41
  • @hagubear HI there. I just re-read my answer, and I don't really see any conflict in light of what you have mentioned. What, specifically, is incorrect? Also, ad hoc queries should only create stubs IF you have "optimize for ad hoc workloads" enabled, but it is disabled by default. – Solomon Rutzky Feb 15 '19 at 15:53
1

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; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.