> For the complete documentation index, see [llms.txt](https://kerryhuangs-organization.gitbook.io/kerry-de-bi-ji-ben/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://kerryhuangs-organization.gitbook.io/kerry-de-bi-ji-ben/sql-server/sql-server-zhuan-xie-stored-procedure-xiao-xi-jie.md).

# \[SQL SERVER]撰寫Stored Procedure小細節

\[SQL SERVER]\[Memo]撰寫Stored Procedure小細節

這篇筆記一下Stored Procedure有那些小細節，也順便提醒自己。

**1.不要忘記 set nocount on:**

SQL Server會針對每個Select 和 DML 回傳訊息給用戶端，

當有設定 nocount on時就可以關閉SQL Server 回傳訊息的行為，

這樣對效能會有不錯的改善，因為網路的傳輸量會降低不少。

**2.如果查詢陳述句太過複雜，請使用SP:**

如果你的商業邏輯複雜導致查詢陳述句過長又龐大，

建議可以使用SP來撰寫，因為用戶端(client)只會傳SP Name給SQL Server(而不是一長串的TSQL)，

所以網路的傳輸量也會降低不少。

**3.請使用兩節式命名:**

相關的物件請都使用兩節式(schema name+object name)命名，

因為這可以直接且明確找到該物件和編譯過的執行計畫，

而省下搜尋其他schema底下可能的object所浪費的資源和時間。

```
 — 兩節式命名 
select * from dbo.test exec dbo.myproc 
 — 避免 
select * from test exec myproc
```

4.stored procedure命名勿使用 sp\_ 開頭: 如果stored procedure使用sp\_開頭，那SQL Server 會先搜尋master database完後， 在搜尋現階段連線的database，

這不僅讓費時間和資源，也增加出錯的機率(如果master database有相同的stroed procedure名稱)

```
--usp開頭create proc dbo.usp_xxx--避免create proc dbo.sp_xxx
```

**5.如果你要執行字串，請使用sp\_executesql取代Execute(Exec) 陳述式**

![img](https://miro.medium.com/max/831/0*auVxrF2Gn5fxjmEK.png)

**(擷取線上叢書..我太懶了XD)，下面附上測試過程。**

```
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.textFROM sys.dm_exec_cached_plans capCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE st.text not like '%sys%'and st.text like '%dbo.test2%'
```

**使用sp\_executesql**

![img](https://miro.medium.com/max/480/0*PVifclkq1S8T6se-.png)

**參數:1和2**

![img](https://miro.medium.com/max/481/0*uxlKmvoGs6YUboTn.png)

**參數:5和6**

**結果:我在SSMS查詢視窗中執行 sp\_executesql 總共4次查詢，**

**可以看到只產生一個執行計畫且重複使用該執行計畫。**

```
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%'
```

**使用exec(Execute)**

![img](https://miro.medium.com/max/370/0*GRXArJLzgBQgM_j3.png)

**參數:1和2**

![img](https://miro.medium.com/max/351/0*tYb4aUs0d9ZkfOiS.png)

**參數:5和6**

**結果:我在SSMS查詢視窗中執行 exec 總共4次查詢，可以看到產生4個執行計畫且個別使用次數為1次。**

**當然!這裡我只是提個大概，因為開發上還是有許多要注意的地方，**

**例如**[**盡量少使用Cursor**](http://www.dotblogs.com.tw/ricochen/archive/2010/11/09/19323.aspx)**，交易處理時請盡量縮短交易…等。**


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://kerryhuangs-organization.gitbook.io/kerry-de-bi-ji-ben/sql-server/sql-server-zhuan-xie-stored-procedure-xiao-xi-jie.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.