撰寫Stored Procedure小細節

 參考引用:[SQL SERVER]撰寫Stored Procedure小細節

---

撰寫Stored Procedure小細節

EXEC AssetLibrary_AssetDeleteAttributes @AssetID

declare @myquery nvarchar(4000);

set @myquery = 'SELECT * from dbo.test2 where c1 = @id';

--第一次

execute sp_executesql @myquery,N'@id int', @id = 1;

--第二次

execute sp_executesql @myquery,N'@id int', @id = 2;

--查看執行計畫

SELECT cap.usecounts as '使用次數',objtype as '快取類型',st.text

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%dbo.test2%'

DECLARE @myquery VARCHAR(8000)

,@myid int;

set @myid = 5;

set @myquery = 'select * from dbo.test2 where c1 = '+ CAST( @myid as varchar);

--第一次

exec (@myquery) 

set @myid = 6; 

set @myquery = 'select * from dbo.test2 where c1 = '+ CAST(@myid as varchar );

--第二次

exec (@myquery) 

SELECT cap.usecounts as '使用次數',objtype as '快取類型',st.text            

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%dbo.test2%'