sql-server - 需要游标来读取提供的参数或所有记录,但不会读取所有
问题描述
当我填写并提供@Tract 参数时,我编写的程序运行良好。但是当我为参数发送一个空值时,过程没有完成,但表是空的。当我传递 null 时,我希望它为所有记录运行。谁能阐明我所缺少的东西?还是我想对光标功能做一个禁止?
ALTER PROCEDURE [dbo].[LoadworkINLoadTagReconciliation]
--Add parameters to procedure
@Tract VARCHAR(15),
@TractType VARCHAR(2),
@Status VARCHAR(1)
AS
--Beginning of procedure
--Process 1: Insert tag records by tract
BEGIN
--Declare Variables and cursor
DECLARE @BeginTagNo INT
DECLARE @EndTagNo INT
DECLARE TagCursor CURSOR
FOR SELECT MIN(BEGINTAGNO), MAX(ENDTAGNO) FROM dbo.INLoadTagReconciliation WHERE BEGINTAGNO <> 0 AND TYPE = 1 AND (TRACTCODE = @Tract OR @Tract = NULL) group by TRACTCODE, TAGKEY
--Check for Existance of workfile, drop and recreate if it exists
IF OBJECT_ID('workINLoadTagReconciliation') IS not NULL
DROP TABLE dbo.workINLoadTagReconciliation
BEGIN
CREATE TABLE workINLoadTagReconciliation (
Tag INT, Type INT, TicketKey INT CONSTRAINT Tag_PK PRIMARY KEY CLUSTERED(Tag)
);
END
--Open cursor holding stored records
OPEN TagCursor
--Load first records into cursor
FETCH NEXT FROM TagCursor
INTO @BeginTagNo, @EndTagNo
--Begin processing records for each tract. It will process how many ever records there are per tract then break out.
WHILE @@FETCH_STATUS = 0
BEGIN
--Here we use a cte (common table expression) to help create the numbering for the rows between the specified beginning and ending tags
WITH
L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Tag FROM L5)
INSERT INTO workINLoadTagReconciliation SELECT Tag, NULL, NULL FROM Nums WHERE Tag >= @BeginTagNo AND Tag <= @EndTagNo ORDER BY Tag
FETCH NEXT FROM TagCursor
INTO @BeginTagNo, @EndTagNo
END
--Now we close the cursor and destroy it with the deallocate command
CLOSE TagCursor
DEALLOCATE TagCursor
解决方案
推荐阅读
- javascript - 如何在javascript中返回嵌套函数的值?
- amazon-redshift - 为数据仓库 ods 层中的每个表创建 1:1 视图
- angular - 如何在Angular 8中映射两个数组
- django - 想要使用 websockets 保存聊天
- asp.net-core - 使用 Sustainsys-Saml2 和 ASP.net Core 动态加载 SAML2 IDP
- python - 在 Python 中使用 np.vectorize 映射布尔值
- google-api - Android SafetyNetClient.attest() 超时
- java - 如何从输入文件中的一行数字中逐个读取数字
- python - 如何在没有任何模型的蓝图中使用烧瓶 sqlalchemy 中的 db.execute
- javascript - HTML 中的自定义属性 (data-) 在 JavaScript 中不起作用