Declare a Cursor From a Table as Variable (In the Select-Statement)

Question

I want to create a SP that have 2 String parameters for 2 table names. In the SP I use dynamic SQL to modify one of the tables, but the other is inside a cursor, and I cant use dynamic SQL after the "FOR"

ALTER PROCEDURE NameProcedure @SourceTable VARCHAR(100),@DestinationTable VARCHAR(100)
AS
BEGIN
DECLARE @AddressSource VARCHAR(100), @AddressDestination VARCHAR(100)
SELECT @AddressSource = '[Test_Toa].[dbo].[' + @SourceTable + ']'
SELECT @AddressDestination = '[Test_Toa].[dbo].[' + @DestinationTable + ']'
--Source Table columns
DECLARE @id int, @idmercado int, @idcadena int, @barcode nvarchar(255),@Complete_P nvarchar(MAX)

DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FOR 
        --HEREE ITS MY PROBLEM :(!!!!!
    SELECT id, idmercado, idcadena, barcode, precios + ',' FROM @AddressSource
OPEN @Cursor
    FETCH NEXT
        FROM @Cursor INTO @id,@idmercado,@idcadena,@barcode,@Complete_P
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --bla bla code
    FETCH NEXT
                FROM @Cursor INTO @id,@idmercado,@idcadena,@barcode,@Complete_P
    END
CLOSE @Cursor
DEALLOCATE @Cursor  


END

I just want to declare a cursor for the table that the user gives

Answer 1

Well, you need to write a dynamic sql statement. Just as an hint. You can copy the values from your given source table into a temp table, generate a cursor on the temp table, iterate through it and deallocate the cursor afterwards and drop the temp table. :-)

Here a short demo code:

DECLARE @sql nvarchar(max), @sourceTable nvarchar(255)

CREATE TABLE dbo.t1(id int, name nvarchar(200))
CREATE TABLE dbo.t2(id int, name nvarchar(200))

SET @sourceTable = N'dbo.t1'

CREATE TABLE #temp(id int, name nvarchar(200))

SET @sql = N'
    INSERT INTO #temp(id,name)
        SELECT id, name
        FROM '+@sourceTable
EXEC(@sql)

DECLARE cur CURSOR FOR
    SELECT id, name
    FROM #temp

OPEN cur

DECLARE @id int, @name nvarchar(200)

FETCH NEXT FROM cur INTO @id, @name

WHILE @@fetch_status = 0 BEGIN
    SELECT @id, @name -- demo output

    FETCH NEXT FROM cur INTO @id, @name
END

-- cleanup
CLOSE cur
DEALLOCATE cur

DROP TABLE dbo.t1
DROP TABLE dbo.t2

DROP TABLE #temp

Beware, I just have written this in notepad without any database. But I'm quite sure it does it's job.

This just works if all available variants of @sourceTable have the same column specification. If not, you need to extract the needed columns from information schema and build a more dynamic code.