Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cursorfetch: The number of variables declared ..

Author  Topic 

dewacorp.alliances

452 Posts
Posted - 2011-01-23 : 23:27:28
Hi there

I have the following stored procedure and if I execute this on our SQL analyser it returns the record no problem what so ever. BUT if I execute this through ASP.NET (I am using EntitySpace objects) to generate this query into data table and it saying that: "Cursorfetch: The number of variables declared in the INTO list must match that of selected columns."

I've discussed this with EntitySpace team and they said it's query issue ?!?!

The environment that we use: .NET 4 EntitySpace 2010 SQL Server 2005 Database

Some people said that I need to review the @ColumnNameRow on the second cursor. But in our case, we need to have construct the query like that due to each table definition is different BUT the numner of column that it's expecting is the same.


DECLARE @sqlstatement2 NVARCHAR(MAX) 
		SET @sqlstatement2 = 'DECLARE  SURVEY_RESULT_CURSOR CURSOR FOR 	
								SELECT tid, FirstName, LastName, Email, ' + @ColumnNameRow + '
								FROM dbo.lime_tokens_' + CAST(@SurveyID AS VARCHAR)  + ' a (NOLOCK) INNER JOIN lime_survey_' + CAST(@SurveyID AS VARCHAR)  + ' b (NOLOCK) ON a.token = b.token
								where Completed IS NOT NULL'

EXEC sp_executesql @sqlstatement2


Any ideas guys? We are stuck with this issue and any guidance or hints that will be good.

Here's the complete stored procedure:


ALTER PROCEDURE [dbo].[rpt_CPRRespondListReport]
(
@SurveyID INT
)
AS
BEGIN
SET NOCOUNT ON;
SET FMTONLY OFF;

DECLARE @SID INT;

SELECT @SID = sid 
FROM lime_Surveys
WHERE sid = @SurveyID

IF (@SID > 1)
BEGIN

    DECLARE @T TABLE
    (
        RecID INT,
        RecID2 INT,
        RecID3 INT,
        RecID4 INT,
        FirstName VARCHAR(40),
        LastName VARCHAR(40),
        Email VARCHAR(320),
        QuestionName VARCHAR(100),
        QuestionValue1 VARCHAR(5),
        QuestionValue2 VARCHAR(5),
        QuestionValue3 VARCHAR(1000),
        QuestionValue4 VARCHAR(5)
    );

    DECLARE @ColumnNameRow VARCHAR(MAX);
    DECLARE @Column_name VARCHAR(32);

    DECLARE @sqlstatement1 NVARCHAR(MAX) 
    SET @sqlstatement1 = 'DECLARE  QuestionCursor CURSOR FOR ' +    
                            'SELECT column_name ' +
                            'FROM information_schema.columns (NOLOCK) ' +
                            'WHERE table_name = ''lime_survey_' + CAST(@SurveyID AS VARCHAR) + ''' AND Ordinal_position >= 10 ' +
                            'ORDER BY ordinal_position ASC'

    EXEC sp_executesql @sqlstatement1

    SET @ColumnNameRow = '';

    OPEN QuestionCursor
    FETCH NEXT FROM QuestionCursor
    INTO @Column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN       
        SET @ColumnNameRow = @ColumnNameRow + '[' + @Column_Name + ']' + ', ' 

        FETCH NEXT 
        FROM QuestionCursor 
        INTO    @Column_name
    END

    SET @ColumnNameRow = SUBSTRING(@ColumnNameRow, 1, LEN(@ColumnNameRow) - 1); 

    CLOSE QuestionCursor
    DEALLOCATE QuestionCursor

    DECLARE @tid INT;
    DECLARE @FirstName VARCHAR(40);
    DECLARE @LastName VARCHAR(40);
    DECLARE @Email VARCHAR(320);
    DECLARE @Q010101 VARCHAR(5);
    DECLARE @Q010102 VARCHAR(5);
    DECLARE @Q010103 VARCHAR(1000);
    DECLARE @Q010201 VARCHAR(5);
    DECLARE @Q010202 VARCHAR(5);
    DECLARE @Q010203 VARCHAR(1000);
    DECLARE @Q010301 VARCHAR(5);
    DECLARE @Q010302 VARCHAR(5);
    DECLARE @Q010303 VARCHAR(1000);
    DECLARE @Q010401 VARCHAR(5);
    DECLARE @Q010402 VARCHAR(5);
    DECLARE @Q010403 VARCHAR(1000);
    DECLARE @Q010501 VARCHAR(5);
    DECLARE @Q010502 VARCHAR(5);
    DECLARE @Q010503 VARCHAR(1000);
    DECLARE @Q010601 VARCHAR(5);
    DECLARE @Q010602 VARCHAR(5);
    DECLARE @Q010603 VARCHAR(1000);
    DECLARE @Q010701 VARCHAR(5);
    DECLARE @Q010702 VARCHAR(5);
    DECLARE @Q010703 VARCHAR(1000);
    DECLARE @Q010801 VARCHAR(5);
    DECLARE @Q010802 VARCHAR(5);
    DECLARE @Q010803 VARCHAR(1000);
    DECLARE @Q010901 VARCHAR(5);
    DECLARE @Q010902 VARCHAR(5);
    DECLARE @Q010903 VARCHAR(1000);
    DECLARE @Q011001 VARCHAR(5);
    DECLARE @Q011002 VARCHAR(5);
    DECLARE @Q011003 VARCHAR(1000);
    DECLARE @Q011101 VARCHAR(5);
    DECLARE @Q011102 VARCHAR(5);
    DECLARE @Q011103 VARCHAR(1000);
    DECLARE @Q011201 VARCHAR(5);
    DECLARE @Q011202 VARCHAR(5);
    DECLARE @Q011203 VARCHAR(1000);
    DECLARE @Q011301 VARCHAR(5);
    DECLARE @Q011303 VARCHAR(1000);


    DECLARE @sqlstatement2 NVARCHAR(MAX) 
    SET @sqlstatement2 = 'DECLARE  SURVEY_RESULT_CURSOR CURSOR FOR  
                            SELECT tid, FirstName, LastName, Email, ' + @ColumnNameRow + '
                            FROM dbo.lime_tokens_' + CAST(@SurveyID AS VARCHAR)  + ' a (NOLOCK) INNER JOIN lime_survey_' + CAST(@SurveyID AS VARCHAR)  + ' b (NOLOCK) ON a.token = b.token
                            where Completed IS NOT NULL'

    EXEC sp_executesql @sqlstatement2 

    OPEN SURVEY_RESULT_CURSOR
    FETCH NEXT FROM SURVEY_RESULT_CURSOR
    INTO @tid, @FirstName, @LastName, @Email, 
         @Q010101, @Q010102, @Q010103, @Q010201, @Q010202, @Q010203, @Q010301, @Q010302, @Q010303, @Q010401, @Q010402, @Q010403,
         @Q010501, @Q010502, @Q010503, @Q010601, @Q010602, @Q010603, @Q010701, @Q010702, @Q010703, @Q010801, @Q010802, @Q010803,
         @Q010901, @Q010902, @Q010903, @Q011001, @Q011002, @Q011003, @Q011101, @Q011102, @Q011103, @Q011201, @Q011202, @Q011203,
         @Q011301, @Q011303 

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @T(RecID, RecID2, RecID3, RecID4, FirstName, LastName, Email, QuestionName, QuestionValue1, QuestionValue2, QuestionValue3) 
        SELECT @tid, 1, 1, 1, @FirstName, @LastName, @Email, 'Mobile Voice Coverage', @Q010101, @Q010102, @Q010103  
        UNION
        SELECT @tid, 1, 2, 1, @FirstName, @LastName, @Email, 'Mobile Data Coverage', @Q010201, @Q010202, @Q010203 
        UNION
        SELECT @tid, 1, 3, 1, @FirstName, @LastName, @Email, 'Mobile Voice Costs', @Q010301, @Q010302, @Q010303 
        UNION
        SELECT @tid, 1, 4, 1, @FirstName, @LastName, @Email, 'Mobile Data Costs', @Q010401, @Q010402, @Q010403 
        UNION
        SELECT @tid, 1, 5, 1, @FirstName, @LastName, @Email, 'International Roaming Costs', @Q010501, @Q010502, @Q010503  
        UNION
        SELECT @tid, 1, 6, 1, @FirstName, @LastName, @Email, 'Handset Quality', @Q010601, @Q010602, @Q010603 
        UNION
        SELECT @tid, 1, 7, 1, @FirstName, @LastName, @Email, 'Mobile Capped Plans', @Q010701, @Q010702, @Q010703 
        UNION
        SELECT @tid, 1, 8, 1, @FirstName, @LastName, @Email, 'Message Bank / Voicemail', @Q010801, @Q010802, @Q010803
        UNION
        SELECT @tid, 1, 9, 1, @FirstName, @LastName, @Email, 'SMS', @Q010901, @Q010902, @Q010903
        UNION
        SELECT @tid, 1, 10, 1, @FirstName, @LastName, @Email, 'Mobile Internet / Email Account', @Q011001, @Q011002, @Q011003
        UNION
        SELECT @tid, 1, 11, 1, @FirstName, @LastName, @Email, 'Access to our office intranet', @Q011101, @Q011102, @Q011103
        UNION
        SELECT @tid, 1, 12, 1, @FirstName, @LastName, @Email, 'Account Management', @Q011201, @Q011202, @Q011203
        UNION
        SELECT @tid, 1, 13, 3, @FirstName, @LastName, @Email, 'Current Provider', @Q011301, null, @Q011303

    FETCH NEXT FROM SURVEY_RESULT_CURSOR
    INTO @tid, @FirstName, @LastName, @Email, 
         @Q010101, @Q010102, @Q010103, @Q010201, @Q010202, @Q010203, @Q010301, @Q010302, @Q010303, @Q010401, @Q010402, @Q010403,
         @Q010501, @Q010502, @Q010503, @Q010601, @Q010602, @Q010603, @Q010701, @Q010702, @Q010703, @Q010801, @Q010802, @Q010803,
         @Q010901, @Q010902, @Q010903, @Q011001, @Q011002, @Q011003, @Q011101, @Q011102, @Q011103, @Q011201, @Q011202, @Q011203,
         @Q011301, @Q011303  

    END
    CLOSE SURVEY_RESULT_CURSOR
    DEALLOCATE SURVEY_RESULT_CURSOR

    SELECT RecID, RecID2, RecID3, RecID4, FirstName, LastName, Email, QuestionName, b.Title AS QuestionValue1, 
            b2.Title AS QuestionValue2, QuestionValue3
    FROM @T a LEFT OUTER JOIN lime_labels b (NOLOCK) ON a.QuestionValue1 = b.Code
            LEFT OUTER JOIN lime_labels b2 (NOLOCK) ON a.QuestionValue2 = b2.Code
    ORDER BY RecID ASC, RecID2 ASC, RecID3 ASC

END
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-01-24 : 10:23:30
the error message is straightforward. your cursor is having more columns than that is declared by variables. the number of variables must be equal to columns that are returned by cursor.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site