SQL之乱笔--游标使用

DECLARE
	@id INT,
	@name NVARCHAR(10)
	
DECLARE cursor_name CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
	SELECT
		id,
		name
	FROM dbo.tablename WITH (NOLOCK)
	ORDER BY id

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name

WHILE (@@FETCH_STATUS = 0) 
BEGIN
    /* your sql commands */

	FETCH NEXT FROM cursor_name INTO @id, @name
END

CLOSE cursor_name
DEALLOCATE cursor_name

------------游标的使用-----------------------

------------游标的使用-----------------------

CREATE TABLE #Shop
(
	BrandShopCode CHAR(9),
	ShopName NVARCHAR(200),
	InDate NVARCHAR(10)
)

DECLARE @BrandCode VARCHAR(4) = 'BC';
DECLARE @ShopCode CHAR(4) = '';
DECLARE @ShopName NVARCHAR(200) = '';
DECLARE @InDateTime DATETIME;

DECLARE cur CURSOR FOR SELECT ShopCode,ShopName,InDateTime FROM Shop WHERE BrandCode=@BrandCode
OPEN cur
FETCH NEXT FROM cur INTO @ShopCode,@ShopName,@InDateTime
WHILE @@FETCH_STATUS=0
BEGIN
	INSERT INTO #Shop(BrandShopCode,ShopName,InDate)
	SELECT SUBSTRING(CAST(@BrandCode AS CHAR(9)),1,2)+'——'+LEFT(@ShopCode,2)+'~'+SUBSTRING(@ShopCode,3,2) AS BrandShopCode
		, CASE WHEN CHARINDEX(@BrandCode,LTRIM(@ShopName))=0 THEN @ShopName 
			ELSE LEFT(LTRIM(@ShopName),2)+'——'+SUBSTRING(@ShopName,3,LEN(@ShopName))
			END AS ShopName
		, CONVERT(NVARCHAR(10),@InDateTime,126) AS InDate
	FETCH NEXT FROM cur INTO @ShopCode,@ShopName,@InDateTime
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM #Shop
DROP TABLE #Shop


--------------------------------