0

I've a cursor which fetch dynamic number of columns because the "SELECT STATEMENT" which I use to declare this cursor is dynamic.

Since I do not know at any point of time, how many columns this cursor will have, I cannot declare fixed number of variables into fetch.

So I have built FETCH statement as dynamic and stored in one @variable... but when i run fetch statement using EXEC sp_executesql its failing with error ..Must declare the scalar variable "@objcursor".

I know that @objcursor variable is not accessible becasue while sp_executesql run which run on isolate THREAD

is there any way someone can advise, how to handle this code to run without an error?

Here is my T-SQL code:

/* ==== Variable Declaration ==== */
    declare @AllValues nvarchar(max)
    declare @objcursor as cursor             
    declare @MonthCount integer 
    declare 
        @vsql        as nvarchar(max)
        ,@vquery    as nvarchar(max)
        ,@id        as int
        ,@value        as varchar(50)

    BEGIN               
        SELECT @AllValues = CASE 
            WHEN t.column_id=1 THEN 
            (COALESCE(@AllValues +'"', '')+ t.name)+'"'
                WHEN t.column_id > 1 THEN 
                (COALESCE(@AllValues + ',"', '') + t.name)+'"' 
                END
            FROM
            (
                SELECT sc.name, sc.column_id FROM sys.objects o
                INNER JOIN sys.columns sc ON o.object_id = sc.object_id
                WHERE o.name = 'temp_daywise' AND o.type = 'U' AND (sc.name like '%Curr Yr%'  or column_id=1)

            ) AS t
            ORDER BY t.column_id
            SET @AllValues='SELECT  "'+@AllValues+' FROM dbo.temp_daywise'

            set @vquery = @AllValues
            set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'

            exec sys.sp_executesql
                @vsql
                ,N'@cursor cursor output'
                ,@objcursor output

            ---Handling Dynamic number of columns in a cursor, get the column count first and build FETCH statement dynamically
            Select @CurCount=COUNT(*) from sys.columns where object_id in(  
                SELECT object_id from sys.objects where name = 'dbo.temp_daywise' and type = 'U'    )
                    and (name like '%Curr Yr%');
            SET @LoopCount = 1      
            --here building my fetch statement
            SET @fetchsql  ='fetch next from @objcursor into @AgreementID'                          
            WHILE @LoopCount <= @CurCount
                BEGIN
                    SET @fetchsql = @fetchsql+','+'@CY_Day'+CONVERT(VARCHAR(2),@LoopCount)                  
                    SET @LoopCount = @LoopCount + 1
                END             
                --EXEC @fetchsql 

                EXEC sp_executesql @fetchsql 

            while (@@fetch_status = 0)
            begin
                BEGIN       
                    'update ...here something'
                END     
            EXEC @fetchsql                          
            end
            close @objcursor
            deallocate @objcursor       
    END

Here is my data and expected resullts:

1) My dynamic cusror will read column name from sys.columns because coulmns are not static that's based on columns count I'm building FETCH statement. following code build cusrsor SELECT statement

SELECT @AllValues = CASE 
                WHEN t.column_id=1 THEN 
                (COALESCE(@AllValues +'"', '')+ t.name)+'"'
                    WHEN t.column_id > 1 THEN 
                    (COALESCE(@AllValues + ',"', '') + t.name)+'"' 
                    END
                FROM
                (
                    SELECT sc.name, sc.column_id FROM sys.objects o
                    INNER JOIN sys.columns sc ON o.object_id = sc.object_id
                    WHERE o.name = 'temp_daywise' AND o.type = 'U' AND (sc.name like '%Curr Yr%'  or column_id=1)

                ) AS t
                ORDER BY t.column_id
                SET @AllValues='SELECT  "'+@AllValues+' FROM dbo.temp_daywise'

                set @vquery = @AllValues
                set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'

                exec sys.sp_executesql
                    @vsql
                    ,N'@cursor cursor output'
                    ,@objcursor output

2) I want to update fetch data into following table for columns Day1...Day31. if cusrsor found 20 columns data will update until CY_Day20.

enter image description here

3) In short, i do not know the cusror retrieving columns at design time so i can't produce fetching variable. Since columns are known at run tiume, i have to build fetch & update statment in while loop as like below:

Note: ignore DECLARE which is on start of the code... but i placed here to get an idea.

DECLARE 
   @CY_Day1 Numeric(18,2), @CY_Day2 Numeric(18,2), @CY_Day3 Numeric(18,2), @CY_Day4 Numeric(18,2), @CY_Day5 Numeric(18,2), 
  , @CY_Day7 Numeric(18,2), @CY_Day8 Numeric(18,2), @CY_Day9 Numeric(18,2), @CY_Day10 Numeric(18,2), @PY_Day10 Numeric(18,2), @CY_Day11 Numeric(18,2), @CY_Day12 Numeric(18,2),........ @CY_Day31 Numeric(18,2)

        Select @CurCount=COUNT(*) from sys.columns where object_id in(  
                SELECT object_id from sys.objects where name = 'dbo.temp_daywise' and type = 'U'    )
                    and (name like '%Curr Yr%');
            SET @LoopCount = 1      
            SET @fetchsql  ='fetch next from @objcursor into @AgreementID'              
            SET @updatesql ='UPDATE  dbo.TPDD_Report_Monthly_Details SET ' 
            WHILE @LoopCount <= 2
                BEGIN
                    SET @fetchsql = @fetchsql+','+'@CY_Day'+CONVERT(VARCHAR(2),@LoopCount)                  
                    SET @updatesql= @updatesql +'CY_Day'+CONVERT(VARCHAR(2),@LoopCount)+' = @CY_Day'+CONVERT(VARCHAR(2),@LoopCount)+',CY_TPDD_Day'+CONVERT(VARCHAR(2),@LoopCount)+' = (@CY_Day'+CONVERT(VARCHAR(2),@LoopCount)+'/1/1),'
                    SET @LoopCount = @LoopCount + 1
                END
                SET @updatesql =@updatesql  + ' dss_update_time = @v_dss_update_time WHERE AgreementId =  @AgreementID and TpddYear=CONVERT(VARCHAR(4),@Current_year)+CONVERT(VARCHAR(4),@Previous_year) and Running_Month = @MonthNo'
                --EXEC @fetchsql 
                PRINT @fetchsql 
                PRINT @updatesql

                ---executing FETCH statement 
                EXEC sp_executesql @fetchsql 

            while (@@fetch_status = 0)
            begin
                BEGIN       
                    ---updating table columns
                    EXEC sp_executesql @updatesql               
                END     
            EXEC @fetchsql                          
            end
            close @objcursor
            deallocate @objcursor

Finally my cusrsor fetch & udpate statement will looks like below:

fetch next from @objcursor into     @AgreementID,@CY_Day1,@CY_Day2,@CY_Day3,@CY_Day4,@CY_Day5,@CY_Day6,@CY_Day7,@CY_Day8,@CY_Day9,@CY_Day10

    UPDATE  dbo.TPDD_Report_Monthly_Details SET 
                                CY_Day1 = @CY_Day1, CY_TPDD_Day1 = (@CY_Day1/1/1),              
                                CY_Day2 = @CY_Day2, CY_TPDD_Day2 = (@CY_Day2/1/1),              
                                CY_Day3 = @CY_Day3, CY_TPDD_Day3 = (@CY_Day3/1/1),              
                                CY_Day4 = @CY_Day4, CY_TPDD_Day4 = (@CY_Day4/1/1),              
                                CY_Day5 = @CY_Day5, CY_TPDD_Day5 = (@CY_Day5/1/1),      
                                CY_Day6 = @CY_Day6, CY_TPDD_Day6 = (@CY_Day6/1/1),  
                                CY_Day7 = @CY_Day7, CY_TPDD_Day7 = (@CY_Day7/1/1),                              
                                CY_Day8 = @CY_Day8, CY_TPDD_Day8 = (@CY_Day8/1/1),                                  
                                CY_Day9 = @CY_Day9, CY_TPDD_Day9 = (@CY_Day9/1/1),
                                CY_Day10 = @CY_Day10, CY_TPDD_Day10 = (@CY_Day10/1/1),                          
                                dss_update_time = @v_dss_update_time                                        
                        WHERE AgreementId =  @AgreementID

Hope I;m able to present my problem correctly.

Improve this question
2
  • When you execute a dynamic sql statement, it's in a different scope so it doesn't work the same. If you want to stick to a cursor, then you're going to have your entire cursor in your execute statement, which can get quite tricky. If you want, post some sample data and your desired results, and I would be glad to help you come up with a simpler solution. Commented Mar 23, 2015 at 17:13
  • Thanks Stephan, I've added expected results and data with my problem statement. Let me know your thoughts. Commented Mar 23, 2015 at 21:57

1 Answer 1

Reset to default
1

I have a good start. You're probably going to have to tweak a few things. I did my best to get it as close as possible as your actual situation. Hope this helps. If you have any questions, let me know.

NOTE I USE THE SAME TABLE NAMES AND DROP THEM.

IF OBJECT_ID('dbo.temp_daywise') IS NOT NULL
    DROP TABLE dbo.temp_daywise;
IF OBJECT_ID('dbo.TPDD_report_Monthly_Details') IS NOT NULL
    DROP TABLE dbo.TPDD_report_Monthly_Details;

CREATE TABLE dbo.temp_daywise
(
    AgreementID CHAR(6),
    RunningMonth INT,
    [Curr Yr1] VARCHAR(100),
    [Curr Yr2] VARCHAR(100),
    [Curr Yr3] VARCHAR(100)
);

INSERT INTO temp_daywise
VALUES  ('A10001',3,'col1_1','col2_1','col3_1'),
        ('A10003',3,'col1_2','col2_2','col3_2'),
        ('A10006',3,'col1_3','col2_3','col3_3'),
        ('A10008',3,'col1_4','col2_4','col3_4');

CREATE TABLE dbo.TPDD_report_Monthly_Details
(
    TpddYear DATE,
    AgreementID CHAR(6),
    RunningMonth INT,
    [CY_Day1] VARCHAR(100),
    [CY_Day2] VARCHAR(100),
    [CY_Day3] VARCHAR(100)
);

INSERT INTO TPDD_report_Monthly_Details
VALUES  ('20131220','A10001',3,NULL,NULL,NULL),
        ('20131220','A10003',3,NULL,NULL,NULL),
        ('20131220','A10006',3,NULL,NULL,NULL),
        ('20131220','A10008',3,NULL,NULL,NULL);

--Now that I've created my versions of your table, here's the actual code

--Variable to hold columns that need to be updated
DECLARE @ColToBeUpdated VARCHAR(MAX);

--Gets your column information for temp_daywise
WITH CTE_temp_daywise_Cols
AS
(
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_NAME = 'temp_daywise'
)

--Now join temp_daywise columns to TPDD_report columns
    --QUOTENAME() add's brackets [] around each column
SELECT  @ColToBeUpdated = COALESCE(@ColToBeUpdated + ',','') + QUOTENAME(A.COLUMN_NAME) + ' = B.' + QUOTENAME(B.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN CTE_temp_daywise_Cols B
    --The "+1" compensates for difference in ordinal positions
    ON A.Ordinal_Position  = B.ORDINAL_POSITION + 1
--This makes the table alisaed A to only get columns for TPDD_report
WHERE   A.TABLE_NAME = 'TPDD_report_Monthly_Details'
        --Don't return AgreementID
        AND A.COLUMN_NAME != 'AgreementID'
        AND B.COLUMN_NAME != 'AgreementID'
ORDER BY A.ORDINAL_POSITION

--Variable to hold code
DECLARE @sql VARCHAR(MAX);
SELECT @sql =  'UPDATE  dbo.TPDD_Report_Monthly_Details 
                SET ' + @ColToBeUpdated +'
                FROM dbo.TPDD_Report_Monthly_Details AS A
                INNER JOIN temp_daywise AS B
                ON A.AgreementID = B.AgreementID'

--Look at code
--Notice you can join on AgreementID and just set the columns equal to each other
SELECT @sql;

--To execute
--EXEC(@sql)

Results stored in @sql:

UPDATE  dbo.TPDD_Report_Monthly_Details 
    SET [RunningMonth] = B.[RunningMonth],
        [CY_Day1] = B.[Curr Yr1],
        [CY_Day2] = B.[Curr Yr2],
        [CY_Day3] = B.[Curr Yr3]
    FROM dbo.TPDD_Report_Monthly_Details AS A
    INNER JOIN temp_daywise AS B
    ON A.AgreementID = B.AgreementID
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Glad to help! And feel free to mark mine as the correct answer so people know it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.