Sql-server – List Prepared Statements

prepared-statementsql serversql-server-2008

Anybody know how I can list the prepared statements that are in a SQL Server instance?

Best Answer

select
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.objtype = 'prepared'

That above query will return all of the prepared plans that are cached, as well as their corresponding SQL text.

Related Solutions

Mysql – How to use prepared statements within MySQL stored procedures

The solution was to SET the variable in the prepared statement itself as in:

SET @sql = CONCAT('SET @curid = SELECT id
                                FROM coupon_operations 
                                WHERE user_id = ? 
                                ORDER BY id ASC 
                                LIMIT ?, 1');

Postgresql – How much should I care about prepared statements for simple queries

The difference is negligible much of the time. You'll see significant benefits if:

There are lots of joins or lots of predicates; or
The query contains long IN lists or similar large data elements

In general, unless it's a "hot" code path where you really hammer a particular query I wouldn't make much effort optimising to focus on re-usable prepared statements.

(You should still parameterise your SQL rather than substituting parameters directly into the text, and people often confuse that with prepraed statements, but you seem to understand the difference.)

Best Answer

Related Solutions

Mysql – How to use prepared statements within MySQL stored procedures

Postgresql – How much should I care about prepared statements for simple queries

Related Question