SQL 存储过程札记

--- 使用游标循环 SQL 查询结果：--- 声明变量DECLARE @bid nvarchar(30);DECLARE @address nvarchar(50);--- 定义游标DECLARE cur CURSOR for 		select bid,[Address]  from building;--- 打开游标OPEN  cur 	FETCH cur INTO @bid, @address		WHILE   @@FETCH_STATUS=0   --- 游标状态	    BEGIN 	        print @bid + ', ' + @address	        	        update B_LDZ set CZWDZ = @address where BID = @bid	        FETCH cur INTO @bid, @address  --- 移到下一位置	    ENDCLOSE cur  --- 关闭游标DEALLOCATE cur  --- 删除游标

二、SQL 解析字符串列表

--- SET @s='appNum1,appNum2,appNum3,appNum4,appNum5,appNum6,appNum7,appNum8,'     CREATE PROCEDURE [dbo].[pro_pre_b_ldz]	@lid nvarchar(20),	@bids  nvarchar(500),	@adds nvarchar(1000)ASBEGINDECLARE @s nvarchar(500)DECLARE @bid nvarchar(30)DECLARE @address nvarchar(50) SET @s= @bidsWHILE CHARINDEX(',',@s)>0         BEGIN             SET @bid = LEFT(@s,CHARINDEX(',',@s)-1)		SET @address = LEFT(@adds,CHARINDEX(',',@adds)-1)				BEGIN 			IF EXISTS (SELECT 1 FROM B_LDZ 				WHERE LID <> @lid AND BID = @bid AND EndDate > GETDATE())			BEGIN				UPDATE B_LDZ SET EndDate = GETDATE()					WHERE LID <> @lid AND BID = @bid AND EndDate > GETDATE()			END			EXEC pro_ldz_czw  @lid,@bid,@address  --- 执行另一存储过程		END         SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(',',@s))             SET @adds=RIGHT(@adds,LEN(@adds)-CHARINDEX(',',@adds))    END ENDGO

2.2 存储过程之 IN 参数

--- 声明临时表及参数declare @TempTable Table(id nvarchar(10),name int)declare @s nvarchar(100)set @s = 'a,b'--- 添加数据insert @TempTable  select 'a',1 union all select 'b',2 	union all select 'c',5 union all select 'd',8	--- 检索数据,相当于 where id in @sselect * from @TempTable where CHARINDEX(','+id+',',',[email protected]+',')>0 /*id	namea	1b	2*/

2.2.2 创建函数分隔字符串

--- 把含分隔符的字符串转换为表--- @str = 'a,b,c,d'CREATE FUNCTION [dbo].[Split]  (  	@str VARCHAR(MAX) ,  	@split VARCHAR(50)  )  RETURNS @t TABLE ( col VARCHAR(50) )  AS  BEGIN      WHILE ( CHARINDEX(@split, @str) <> 0 )          BEGIN              INSERT  @t( col )              VALUES  ( SUBSTRING(@str, 1, CHARINDEX(@split, @str) - 1) )              SET @str = STUFF(@str, 1, CHARINDEX(@split, @str), '')          END      INSERT  @t( col ) VALUES  ( @str )      RETURN  END 

--- 测试select * from dbo.split('a,b,c,d' , ',')/*col-----abcd*/

应用时使用：select * from table where id in dbo.split('a,b,c')