很多小細節在Microsoft的文件中有做記載,
但很多時候會因閱讀的慣性而被忽略.
本文就是描述一個容易忽略的地雷......
<<因頁面顯示也有限制, 建議實做會更容易看出問題.>>
一般在進行T-SQL的code review, 或是進行現有的程序語法 (store procedure、function、trigger…) 的調校時, 會需要把T-SQL的程式碼取出來檢視, 可以透過UI去產生指令碼, 也可以使用sp_helptext的方式快速列出程式碼.
參考資訊: sp_helptext (Transact-SQL)
雖然參考資訊中寫的很清楚, 但多數不會有人一字一句的全看完, 多數知道「喔~語法這樣下」就去做了, 直到碰到了問題才會回來看….. sp_helptext是有每行的回傳限制字數, 255個字幅.
這就有一個血淋淋的例子
/********************************************************************************
說明: sp_helptext產生的output字串長度, 單行最長只支援至255字元
********************************************************************************/
use DEMO;
go
create or alter procedure One_line_over_255_char
as
begin
--下一行的註解長度會拉至超過255個字元, 而剛好切在另一個where被註解掉的位置上
select column1, column2 from sample_table --AAAAQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCV--WHERE column1 = 100;
end;
go
上面這個程式碼, 其中一行的字數超過255個字幅, 而且剛好切在另一個應是註解的 --WHERE 條件中, 此時如果使用sp_helptext去列出這支store procedure的程式碼的話, 結果就會和原意不相同.
/* 使用sp_helptext列出procedure的內容 */
exec sp_helptext One_line_over_255_char;
go
/*
sp_helptext打印出的字串, 單行最長的長度只有255個長度
以這個例子, 一個長字串為註解, 同時最後的WHERE條件只是註解的一部份
但WHERE開始於第256個字元, 就會被截到下一行.
如果沒有回頭去比對, 那麼就有可能對於這個output的語法, 做出錯誤的判斷.
create procedure One_line_over_255_char
as
begin
select column1, column2 from sample_table --AAAAQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCV--
WHERE column1 = 100;
end;
*/
這時看起來這個語法變成了帶有WHERE條件的描述.
而若是使用產生指令碼的方式的話, 則沒有此問題.
/* ==指令碼參數==
來源伺服器版本 : SQL Server 2016 (13.0.4001)
來源資料庫引擎版本 : Microsoft SQL Server Enterprise Edition
來源資料庫引擎類型 : 獨立 SQL Server
目標伺服器版本 : SQL Server 2016
目標資料庫引擎版本 : Microsoft SQL Server Enterprise Edition
目標資料庫引擎類型 : 獨立 SQL Server
*/
USE [DEMO]
GO
/****** Object: StoredProcedure [dbo].[One_line_over_255_char] Script Date: 2017/10/21 下午 04:15:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[One_line_over_255_char]
as
begin
--下一行的註解長度會拉至超過255個字元, 而剛好切在另一個where被註解掉的位置上
select column1, column2 from sample_table --AAAAQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCV--WHERE column1 = 100;
end;
GO