пятница, 29 марта 2013 г.

Как параметризовать запросы




DBCC FREEPROCCACHE
SELECT *
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 1

 SELECT *
 FROM sys.dm_exec_cached_plans

;WITH Cache
AS
(
 SELECT usecounts, cacheobjtype, objtype, text
 FROM sys.dm_exec_cached_plans
  CROSS APPLY sys.dm_exec_sql_text(plan_handle)
 WHERE usecounts > 1
)
SELECT * FROM Cache
WHERE text LIKE '%dm_os_buffer_descriptors%' AND text NOT LIKE '%WITH Cache%'
ORDER BY usecounts DESC

-- Использование функции sp_executesql

DBCC FREEPROCCACHE
EXEC sp_executesql N'
 SELECT *
 FROM sys.dm_os_buffer_descriptors
 WHERE database_id = @ID'
  ,N'@ID INT'
  ,@ID = 1

;WITH Cache
AS
(
 SELECT usecounts, cacheobjtype, objtype, text
 FROM sys.dm_exec_cached_plans
  CROSS APPLY sys.dm_exec_sql_text(plan_handle)
 WHERE usecounts > 1
)
SELECT * FROM Cache
WHERE text LIKE '%dm_os_buffer_descriptors%' AND text NOT LIKE '%WITH Cache%'
ORDER BY usecounts DESC