none
Cursor - Declare a new one without knowing the the table and the column RRS feed

  • Question

  • Hi all,

    i need to create a new cursor (i already know hot to create and use it knowing the table and the column involved) without knowing the table involved and then the column present in the table.

    I suppose that i need to cycle the table to 'read' all the column, and assign it to a variable (each variable for each column present) but i don't know how. 

    Anyone can help me?

    Wednesday, September 11, 2019 10:10 AM

All replies

  • When declaring a cursor, you need to know this. You may use dynamic SQL.

    But I have to ask: What is your use-case?

    Cause in many scenarios of such kind, people mix deployment with run-time. Cause it is absolutely okay to use dynamic SQL during deployment to create permanent procedures for use during runtime.

    Wednesday, September 11, 2019 10:23 AM
  • i need to create a new cursor ... without knowing the table involved and then the column present in the table.

    That's impossible, the table & column Name have to be fixed define in the cursor declaration, you can not work with variable here.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, September 11, 2019 10:28 AM
  • I need to create a stored procedure to be used in azure, to use many time, so that's why i need to generalize it...

    this is the code with fixed parameters:

    CREATE OR ALTER PROCEDURE procedure_name
    @schema_name varchar(255)
    ,@table_source varchar(32)


    AS
    BEGIN


    DECLARE @log1 AS VARCHAR(200)
    DECLARE @log2 AS VARCHAR(200)
    DECLARE @log3 AS VARCHAR(200)
    DECLARE @log4 AS VARCHAR(200)
    DECLARE @log5 AS VARCHAR(200)

    DECLARE merge_cursor CURSOR STATIC
    FOR SELECT *
    FROM tab_source
    OPEN merge_cursor;

    FETCH NEXT FROM merge_cursor INTO @log1, @log2, @log3, @log4, @log5
    WHILE @@FETCH_STATUS = 0

    BEGIN
    IF EXISTS (
    SELECT col1
    ,col2
    ,col3
    ,col4
    FROM tag_target
    WHERE ID=@log1
    )
    BEGIN 
    BEGIN TRY
    UPDATE tab_target
    SET col1= @log2
    ,col2= @log3
    ,col3= @log4
    ,col4= @log5
    WHERE ID=@log1
    END TRY
    BEGIN CATCH
    INSERT INTO tab_error -- to replace whit an update in the source table
    SELECT col1
    ,col2
    ,col3
    ,col4
    ,col5
    FROM tab_source
    WHERE col1= @log2
    END CATCH
    END
    ELSE
    BEGIN
    BEGIN TRY
    INSERT INTO tab_target 
    SELECT col1
    ,col2
    ,col3
    ,col4
    ,col5
    FROM tab_source
    WHERE @log1 = ID
    END TRY
    BEGIN CATCH
    INSERT INTO tab_error 
    SELECT col1
    ,col2
    ,col3
    ,col4
    ,col5
    FROM tab_source
    WHERE col1= @log2
    END CATCH
    END

    FETCH NEXT FROM merge_cursor INTO @log1, @log2, @log3, @log4, @log5
    END

    CLOSE merge_cursor
    DEALLOCATE merge_cursor
    END

    Wednesday, September 11, 2019 10:35 AM
  • I need to create a stored procedure to be used in azure, to use many time, so that's why i need to generalize it...

    Then SQL is the wrong tool, we don't generalize with SQL on that level. SQL is not designed and built for this.

    The reason for this is simple: Schemas don't change. They are invariant.

    Again: Do you look at the correct aspects of the problem? Do you have the correct view point? What is your actual use-case?

    You can do it by using dynamic SQL. But from the architectural viewpoint this is only a good idea during deployment.

    For example: The common use-case for a procedure like yours means that you need to create that cursor as batch on the client and execute it. There is no gain in a general/abstract procedure.

    Wednesday, September 11, 2019 12:35 PM
  • Hi,

    SQL Server is very limited as far as declaring and using cursors. Oracle (and Postgres) offers many styles of declaring cursors where you can pass parameters and don't need to know actual values before declaring the cursors etc. Good luck.  


    Thursday, September 12, 2019 5:51 AM
  • However, i wrote the portion of code where i create dinamically the variable, based of the number of column in a table. Here it is:

    DECLARE @counter INT;
    DECLARE @sql_for_declare NVARCHAR(MAX) = N'DECLARE @source_table_column'
    DECLARE @sql_for_type NVARCHAR(MAX) = N' NVARCHAR(100)'

    SET @counter = (SELECT COUNT(COLUMN_NAME) AS NUMBER_OF_COLUMN
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'schema_name_to_declare_in_sp' AND TABLE_NAME = 'table_name_to_declare_in_sp')

    WHILE @counter <> 0
    BEGIN
    set @sql_for_declare = @sql_for_declare + CAST(@counter AS NVARCHAR(30)) + @sql_for_type
    PRINT @sql_for_declare
    EXEC sp_executesql @sql_for_declare
    SET @sql_for_declare = N'DECLARE @source_table_column'
    SET @counter = @counter - 1;
    END

    Thursday, September 12, 2019 9:34 AM
  • >> I need to create a new cursor (I already know how to create and use it knowing the table and the column involved) without knowing the table involved and then the column present in the table. <<

    I see you have zero points, so you might be so new to SQL that you have no idea how much we hate cursors. SQL is supposed to be a set oriented language not a "row by row" language like filesystems and punch cards.

    But ignoring that, and assuming you actually have a valid reason for using the cursor (I've written five of them in 30+ years of using this language and I know I could've avoided at least three of them if we had had the modern features the current SQL back in SQL-86), why don't you know what your table is? This is just bad programming and has nothing to do with SQL. What you're trying to write is a "automobiles squids and Lady Gaga" module of code. 

    What exactly are you trying to do? Write some kind of general purpose tool? Some of these already exist and you shouldn't be writing them for yourself

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 12, 2019 5:00 PM