SQL Cursor提取错误

Question

我创建了这个函数来支持我的查询生成器。当我测试这个函数时，我会得到以下错误：

INTO列表中声明的变量数量必须与所选列的数量相匹配。可能有人可以为我纠正或告诉我这里做错了什么？

USE [TestDB]
GO
/****** Object:  UserDefinedFunction [dbo].[Acc_Query_CustID]   
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
 GO

  Alter FUNCTION [Acc_Query_CountryData]
 (
@Service_ID int --, @Postal_Start varchar(8). @Postal_Stop varchar(8), @AdrID int,      Type nvarchar(50), @Alias nvarchar(255))
          RETURNS nvarchar(800)
     AS
     BEGIN
-- Declare the return variable here
declare @Query nvarchar(800)
set @Query = ''
declare @country nvarchar(255)
declare @postal_SP nvarchar(255)
declare @postal_ST nvarchar(255)
declare @adrID int
declare @type nvarchar(50)
declare @alias nvarchar(255)
declare @postalQuery nvarchar(255)
declare @TypeExcl bit
declare @AliasExcl bit
declare @AdridExcl bit
---------------------------------------------------     
declare Accountring_Country1Cursor Cursor for 
        select Country from dbo.Accounting_Country1 where Service_ID = @Service_ID        
OPEN Accountring_Country1Cursor
FETCH NEXT FROM Accountring_Country1Cursor 
INTO @country
WHILE @@FETCH_STATUS = 0
BEGIN
set @postalQuery= ' AND (Country =' +@country               
FETCH NEXT FROM Accountring_Country1Cursor 
INTO @country
--------------------------------------------------- 
--------------------POSTAL  
    declare Accounting_PostalCursor Cursor for
    select POSTAL_START, isnull(POSTAL_STOP,'') as POSTAL_STOP  from dbo.Accounting_Postal where Service_ID =  @Service_ID and Country_ID = @country
    OPEN Accounting_PostalCursor
    FETCH NEXT FROM Accounting_PostalCursor 
    INTO @postal_SP, @postal_ST
    WHILE @@FETCH_STATUS = 0
    BEGIN
       set @Query= @Query + ' AND (Postal '+ @postal_ST             
        FETCH NEXT FROM Accounting_PostalCursor 
        INTO @postal_SP, @postal_SP
    END 
    CLOSE Accounting_PostalCursor
    DEALLOCATE Accounting_PostalCursor 
    ------------------TYPE
    declare Accounting_TypeCursor Cursor for
    select Type  from dbo.Accounting_Type where Service_ID =  @Service_ID and Country_ID = @country
    OPEN Accounting_PostalCursor
    FETCH NEXT FROM Accounting_TypeCursor 
    INTO @type
    WHILE @@FETCH_STATUS = 0
    BEGIN   
       set @Query= @Query + ' AND (Type '+ (case(@TypeExcl) when 1 then ' NOT LIKE ' else ' LIKE ' end) + @type                 
        FETCH NEXT FROM Accounting_TypeCursor 
        INTO @type
    END 
    CLOSE Accounting_TypeCursor
    DEALLOCATE Accounting_TypeCursor 
    -- <ADRP> <ADRD>.Adrid =
    ------------------ALIAS
    declare Accounting_AliasCursor Cursor for
    select Alias from dbo.Accounting_Alias where Service_ID =  @Service_ID and Country_ID = @country
    OPEN Accounting_PostalCursor
    FETCH NEXT FROM Accounting_TypeCursor 
    INTO @alias
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @Query= @Query + ' AND (alias '+ (case(@AliasExcl) when 1 then ' NOT LIKE ' else ' LIKE ' end) + @alias                     
        FETCH NEXT FROM Accounting_AliasCursor 
        INTO @alias
    END 
    CLOSE Accounting_AliasCursor
    DEALLOCATE Accounting_AliasCursor 

    ------------------ ADDRESSID
    declare Accounting_AdridCursor Cursor for
    select AdrID from dbo.Accounting_AdrID where Service_ID =  @Service_ID and Country_ID = @country
    OPEN Accounting_AdridCursor
    FETCH NEXT FROM Accounting_AdridCursor 
    INTO @adrid
    WHILE @@FETCH_STATUS = 0
    BEGIN
       set @Query=@Query + ' AND (adrid '+ (case(@adridExcl) when 1 then ' NOT LIKE ' else ' LIKE ' end) + @adrid       

        FETCH NEXT FROM Accounting_AdridCursor 
        INTO @adrid
    END 
    CLOSE Accounting_AdridCursor
    DEALLOCATE Accounting_AdridCursor 
--------------------------------------------------- 
---------------------------------------------------             
END 
CLOSE Accountring_Country1Cursor
DEALLOCATE Accountring_Country1Cursor               
RETURN @Query

END

Answer 1

FETCH INTO子句中的列数应该与用于创建游标的SQL语句中定义的列数相同。因此，对于Accountring_Country1Cursor，您应该指定(并首先声明)另外2个变量：

FETCH NEXT FROM Accountring_Country1Cursor 
INTO @country, @isDelivery, @exclude

或者重新定义游标，以便检索单个列：

declare Accountring_Country1Cursor Cursor for 
select Country 
from dbo.Accounting_Country1 
where Service_ID = @Service_ID  

Answer 2

检查线：

OPEN Accounting_PostalCursor

你宣布一个"Accounting_TypeCursor“

declare Accounting_TypeCursor Cursor for
select Type
from dbo.Accounting_Type 
where Service_ID =  @Service_ID and Country_ID = @country

然后你打开另一个光标：

开放Accounting_PostalCursor

"Accounting_AliasCursor“也是如此。