一、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 --- 移到下一位置 END CLOSE cur --- 关闭游标 DEALLOCATE cur --- 删除游标
二、SQL 解析字符串列表
2.1 遍历字符串列表
--- SET @s='appNum1,appNum2,appNum3,appNum4,appNum5,appNum6,appNum7,appNum8,' CREATE PROCEDURE [dbo].[pro_split_str] @s nvarchar(1000) AS BEGIN DECLARE @bid nvarchar(30) SET @s= 'appNum1,appNum2,appNum3,appNum4,appNum5,appNum6,appNum7,appNum8,' WHILE CHARINDEX(',',@s)>0 BEGIN SET @bid = LEFT(@s,CHARINDEX(',',@s)-1) print @bid --- 输出分隔内容,执行SQL或存储过程 SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(',',@s)) END END GO
2.2 存储过程之 IN 参数
2.2.1 CHARINDEX 分隔字符串
--- 声明临时表及参数 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 @s select * from @TempTable where CHARINDEX(','+id+',',','+@s+',')>0 /* id name a 1 b 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 ----- a b c d */
应用时使用:select * from table where id in dbo.split('a,b,c')