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
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