T-SQL, SQL Server 2008, Local Cursor, Dynamic Cursor

gistfile1.txt

-- ===================================================
-- Description:	cursor, sp_executesql, transaction
-- ===================================================
CREATE PROCEDURE [dbo].[sp_MyProcedure]
 @sub_system CHAR(2)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	-- resource
	DECLARE @sql NVARCHAR(MAX);

	-- fields
	DECLARE @file_no INT;
	DECLARE @card_no VARCHAR(16);
	DECLARE @auto_closed CHAR(1);

	--# 取值
	DECLARE C_EOD36 CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
		SELECT FILE_NO, CARD_NO, AUTO_CLOSED FROM vwFOSR0036

	--# transaction
	BEGIN TRAN
	OPEN C_EOD36;
	WHILE 1=1
	BEGIN
		-- fetch one row
		FETCH C_EOD36 INTO  @file_no, @card_no, @auto_closed;
		IF @@FETCH_STATUS != 0 BREAK; -- until not found.
		-- trace
		PRINT '@file_no: ' + Cast(@file_no AS VARCHAR(10)) + ', @card_no: ' + @card_no + ', @auto_closed: ' + @auto_closed

		-- proceed one row

		--# 已自動結案過的不可再結案。
		IF @auto_closed = 'Y' CONTINUE; -- skip this row

		--# 自動結案
		SET @sql = N'UPDATE T_ChargeBack_' + @sub_system 
			+ N' SET IS_CLOSED = 1'
			+ N', CLOSED_DATE = CONVERT(VARCHAR(10), GETDATE(), 111)'
			+ N', AUTO_CLOSED = ''Y'''
			+ N', MEMO = Cast(MEMO AS NVARCHAR(MAX)) + CHAR(13) + CHAR(10) + ''auto closed ok'' '
			+ N'WHERE FILE_NO = @file_no; '
		PRINT '@sql → ' + @sql;
		EXEC sp_executesql @sql, N'@file_no INT', @file_no;

	END
	CLOSE C_EOD36;
	COMMIT;
END

SQL Server 2008 Cursor Example.sql

--===========================================================
-- To count all tables rows of current database
--===========================================================

-- resource
DECLARE @SqlCmd NVARCHAR(500);
DECLARE @TableName VARCHAR(100), @SchemaName VARCHAR(50),@RowCnt BIGINT;
DECLARE @TableRowCnt TABLE 
		( TableName VARCHAR(100)
		, SchemaName VARCHAR(50)
		, RowCnt BIGINT);

-- declare local cursor
DECLARE c_tables CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT TableName = T.name
		 , SchemaName = S.name  
	  FROM sys.tables T
	  INNER JOIN sys.schemas S ON T.schema_id = S.schema_id 
	  WHERE T.is_ms_shipped = 0 
	    AND S.name = 'dbo';

-- go
OPEN c_tables;
WHILE 1=1
BEGIN
	-- fetch one row
	FETCH c_tables INTO @TableName, @SchemaName;
	IF @@FETCH_STATUS != 0 BREAK; -- until not found.

	-- proceed one row
	SET @SqlCmd = 'SELECT @RowCnt = COUNT(*) FROM ' + @SchemaName + '.' + @TableName;
	PRINT @SqlCmd;	
	
	EXEC sp_executesql @SqlCmd, N'@RowCnt BIGINT OUTPUT', @RowCnt OUTPUT;
	IF @@ERROR != 0 BREAK;
	
	INSERT INTO @TableRowCnt VALUES (@TableName, @SchemaName, @RowCnt);
END

-- show result
SELECT * FROM @TableRowCnt;

SQL Server 2008 Dynamic Cursor Example.sql

--===========================================================
-- To count all tables rows of your database
--===========================================================

-- arguments
DECLARE @DbName VARCHAR(50) = 'your database name';

-- resource
DECLARE @SqlCmd NVARCHAR(500);
DECLARE @TableName VARCHAR(100), @SchemaName VARCHAR(50),@RowCnt BIGINT;
DECLARE @TableRowCnt TABLE 
		( TableName VARCHAR(100)
		, SchemaName VARCHAR(50)
		, RowCnt BIGINT);

-- declare dynamic cursor & open that.
DECLARE @DynamicCursor CURSOR;
SET @SqlCmd = N'SET @Cursor = CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT TableName = T.name, SchemaName = S.name  
	  FROM ' + @DbName + '.sys.tables T
	  INNER JOIN ' + @DbName + '.sys.schemas S 
		 ON T.schema_id = S.schema_id 
	  WHERE T.is_ms_shipped = 0 AND S.name = ''dbo'';  
	OPEN @cursor;';

PRINT @SqlCmd;
EXEC sp_executesql @SqlCmd, N'@Cursor CURSOR OUTPUT', @DynamicCursor OUTPUT;
IF @@ERROR != 0 RETURN; -- fail & leave

-- go
WHILE 1=1
BEGIN
	-- fetch one row
	FETCH @DynamicCursor INTO @TableName, @SchemaName;
	IF @@FETCH_STATUS != 0 BREAK; -- until not found

	-- proceed one row	
	SET @SqlCmd = 'SELECT @RowCnt = COUNT(*) FROM ' + @DbName + '.' + @SchemaName + '.' + @TableName;
	PRINT @SqlCmd;	
	
	EXEC sp_executesql @SqlCmd, N'@RowCnt BIGINT OUTPUT', @RowCnt OUTPUT;
	IF @@ERROR != 0 BREAK;
	
	INSERT INTO @TableRowCnt VALUES (@TableName, @SchemaName, @RowCnt);
END

-- show result
SELECT * FROM @TableRowCnt;