游标那点事儿

两种循环跳出方法

1:稍显复杂点：

CREATE  PROCEDURE dbo.usp_crAllTables

@Client_ID VARCHAR(256)
AS
DECLARE @table_name VARCHAR(50)
SET NOCOUNT ON 
DECLARE T_Name_Cur CURSOR LOCAL STATIC FORWARD_ONLY FOR 
SELECT name FROM 
(VALUES ('Jim'),('Tom'),('Anna')) AS a (name)
OPEN T_Name_Cur 
WHILE 1=1
BEGIN 
FETCH NEXT FROM T_Name_Cur INTO @table_name
IF @@FETCH_STATUS<>0 BREAK
PRINT @table_name
END 
CLOSE T_Name_Cur

DEALLOCATE T_Name_Cur

重点在于break放置位置，避免重复取最后一行，这两种方法的print那里就是我们想要循环执行东东的地方。

2.直接用@@fetch_status 作为循环终止条件

 

SET NOCOUNT ON

DECLARE T_Name_Cur CURSOR LOCAL STATIC FORWARD_ONLY FOR

SELECT name FROM

(VALUES ('Jim'),('Tom'),('Anna')) AS a (name)

OPEN T_Name_Cur

FETCH NEXT FROM T_Name_Cur INTO @table_name

WHILE (@@fetch_status=0)

BEGIN

PRINT @table_name

FETCH NEXT FROM T_Name_Cur INTO @table_name

END

CLOSE T_Name_Cur

DEALLOCATE T_Name_Cur

需要记得想要在循环内执行的东西要放到两个fetch next之间才好。

实践，游标加上动态SQL 实现对某表的所有字段进行更新的操作

CREATE PROCEDURE Clean_up_raw_tables 
@Table_Name VARCHAR(256) 
as 
DECLARE @NAME VARCHAR(256), 
@SQL VARCHAR(MAX) 
DECLARE RE_CUR CURSOR LOCAL STATIC FORWARD_ONLY FOR 
SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@Table_Name) AND NAME!='ID' 

OPEN RE_CUR 
WHILE (1=1) 
BEGIN 
FETCH NEXT FROM RE_CUR INTO @NAME 
IF @@FETCH_STATUS<>0 BREAK 
SET @SQL='UPDATE '+@Table_Name+' SET '+@NAME+'=REPLACE('+@NAME+',''"'','''')' 
PRINT @SQL 
EXEC(@SQL) 
END 
CLOSE RE_CUR 
DEALLOCATE RE_CUR