CODEWITHSUNDEEP
sqlfunctioncursorfetch
pancake
pancake

Reputation: 600

SQL Cursorfetch Error

I have created this function to support my query builder. When I test this function I get the following error:

Cursorfetch: The number of variables declared in the INTO list must match that of selected columns. Maybe someone can correct for me or tell me what I'm doing wrong here?

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

Upvotes: 0

Views: 150

Answers (2)

bjnr
bjnr

Reputation: 3437

Check line:

OPEN Accounting_PostalCursor

You declare a "Accounting_TypeCursor"

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

and they you open another cursor:

OPEN Accounting_PostalCursor

The same with "Accounting_AliasCursor".

Upvotes: 0

bjnr
bjnr

Reputation: 3437

The number of columns in FETCH INTO clause should be the same as the number of columns defined in SQL statement used to create the cursor. So for Accountring_Country1Cursor you should specify (and declare first) 2 more variables:

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

Or redefine cursor, so it retrieves a single column:

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

Upvotes: 1

Related Questions