一、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')