存儲過程不支持?jǐn)?shù)組類型參數(shù)的變通辦法

　　項目——網(wǎng)絡(luò)社區(qū)(ASP.Net)，其中涉及到一個很常見卻也很重要的功能就是“批量刪除”。

　　用ASP開發(fā)網(wǎng)上商城時是使用的Access數(shù)據(jù)庫，當(dāng)時并不知道Access有存儲過程，更準(zhǔn)確的說法是：那時候根本就不知道什么是存儲過程。所以在做批量刪除是使用的拼SQL語句的做法

　　Delete SystemLog Where ID = 1 or ID = 2 or ID = 3;

　　等價于

　　Delete SystemLog Where ID in(1,2,3);

　　這樣的設(shè)計有兩個缺陷：

　　1、暴露了數(shù)據(jù)庫中表的結(jié)構(gòu);

　　2、執(zhí)行速度慢，因為“or”會引起全表掃描，導(dǎo)致表索引實效，所以就算ID是索引也無濟(jì)于事。

　　既然知道了這種設(shè)計的不足，那么這次項目當(dāng)然就不能濤聲依舊了，現(xiàn)在使用的開發(fā)語言是C#，數(shù)據(jù)源是SQL Server 2005。那么第一個問題也就很好解決了，使用存儲過程

　　Create Procedure Pro_DeleteLog

　　(

　　@Qualification int

　　)

　　As

　　Begin

　　Delete SystemLog Where ID = @Qualification;

　　End

　　Go

　　在C#代碼中調(diào)用Pro_DeleteLog，即使這條信息被截獲，也不會暴露表的結(jié)構(gòu)，但新的問題隨之而來，如果這樣做的話，勢必速度會很慢，雖然用戶感覺是批量刪除，但實際上還是一條一條的刪除。

　　如果一次性刪除10條記錄是不是要循環(huán)調(diào)用這個存儲過程10次?也就是鏈接數(shù)據(jù)庫10次，要知道每鏈接數(shù)據(jù)庫一次，都是有性能開銷的。要是一次性刪除100條、1000條呢?顯然這種方法是不行的，最佳的做法就是一次性把所有要刪除的記錄的ID一次傳給這個存儲過程，但在SQL Server 2005中存儲過程是不支持?jǐn)?shù)組類型的參數(shù)的(其他的數(shù)據(jù)庫不是蠻清楚)，那么能不能變通一下呢?把所有要刪除的記錄的ID拼成一個形如“1,2,3,4,5,6,”的字符串，然后在傳給存儲過程，在存儲過程中進(jìn)行處理，整個過程只需要連接數(shù)據(jù)庫一次，就著一方面來說速度就提升不少。

　　然而好事多磨，數(shù)據(jù)庫中沒有提供C#中“Split”的功能函數(shù)，對于字符串只有SubString、Left、Right等函數(shù)，沒關(guān)系，有這些就夠了，我們利用這些內(nèi)置函數(shù)進(jìn)行巧妙的組合也能實現(xiàn)Split功能!

　　代碼如下： Create Procedure Pro_DeleteLog

　　(

　　@Qualification varChar(Max)

　　)

　　As

　　Begin

　　Declare @i int;

　　Declare @Start int;

　　Declare @Length int;

　　Declare @TotalLenght int;

　　Set @i = 1;

　　Set @Start = 1;

　　Set @Length = 0;

　　Set @TotalLenght = Len(@Qualification);

　　While(@i <= @TotalLenght)

　　Begin

　　If(SubString(@Qualification, @i, 1) = ',')

　　Begin

　　Delete SystemLog Where ID = SubString(@Qualification, @Start, @Length);

　　Set @Start = @i + 1;

　　Set @Length = 0;

　　End

　　Else

　　Begin

　　Set @Length = @Length + 1;

　　End

　　Set @i = @i + 1;

　　End

　　If(Right(@Qualification, 1) <> ',')

　　Begin

　　Delete SystemLog Where ID = SubString(@Qualification, @Start, @Length);

　　End

　　End

　　Go

　　怎么樣，是不是很簡單。ID是主鍵，在SQL Server 2005中主鍵默認(rèn)是索引，用索引查找速度有著明顯的提升，這樣一來，安全、速度問題得以完美解決，呵呵

　　附：

　　If(Right(@Qualification, 1) <> ',')

　　Begin

　　Delete SystemLog Where ID = SubString(@Qualification, @Start, @Length);

　　End

　　其實可以不要的，因為在程序代碼中通過循環(huán)拼接起來的字符串最后一位本來就是“,”，沒有必要在程序那邊想辦法把最后一位去掉，然后又在存儲過程這邊又來判斷最后一位是否為“,”。

　　這里給出代碼僅僅只為了編程思想的嚴(yán)謹(jǐn)，無論最后一位是否為“,”都可以分割，否則當(dāng)最后一位不是“,”時，那么最后一個“,”后的字符將全部被丟棄。