SQL Dynamic Schema Name in Cursor

Question

I want to use Cursor with dynamic SchemaName.

In my below Code SELECT id FROM @schema_names.company line is my issue. @schema_names is dynamic Schema Name.

How to use Dynamic Schema/Table Name in Cursor?

-- in-memory schema table to hold distinct schema_names
DECLARE @i int
DECLARE @numrows int
DECLARE @schema_names nvarchar(max)
DECLARE @schema_table TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , schema_names nvarchar(max)
)
DECLARE @company_id NVARCHAR(max) 

-- populate schema table
INSERT @schema_table
SELECT name FROM sys.schemas Where name <> 'dbo' AND name <> 'guest' AND name <> 'INFORMATION_SCHEMA' AND name <> 'db_accessadmin' AND name <> 'db_backupoperator' AND name <> 'db_datareader' AND name <> 'db_datawriter' AND name <> 'db_ddladmin' AND name <> 'db_denydatareader' AND name <> 'db_denydatawriter' AND name <> 'db_owner' AND name <> 'db_securityadmin' AND name <> 'sys'

select * from @schema_table
-- enumerate the table

SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @schema_table)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM @schema_table))
    BEGIN

        -- get the next record primary key
        SET @schema_names = (SELECT schema_names FROM @schema_table WHERE idx = @i)

        DECLARE my_cursor CURSOR local static read_only forward_only FOR 
          SELECT id FROM @schema_names.company 

        OPEN my_cursor 

        FETCH next FROM my_cursor INTO @company_id 

        WHILE @@FETCH_STATUS = 0 
          BEGIN 
              --Do something with Id here 
              PRINT @company_id + 'a' 
              FETCH next FROM my_cursor INTO @company_id 
          END 

        CLOSE my_cursor 
        DEALLOCATE my_cursor 

        BEGIN TRY
            DECLARE @sSQL nvarchar(500);            
            SELECT @sSQL = N'INSERT ['+@schema_names+'].[Menu] VALUES (9, N''Dashboard'', N''Charts'', N''/Dash/Chart'', 1)'
            EXEC sp_executesql @sSQL
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE()+' '+@schema_names AS ErrorMessage;
        END CATCH

        -- increment counter for next record
        SET @i = @i + 1
    END

Valerica · Accepted Answer · 2018-02-01 12:42:22Z

2

try This approach

1 - Create a Temp Table

2 - Insert the Id from your Table to the Temp table using Dynamic SQL

3 - Fetch Cursor from Temp Table

CREATE TABLE #Temp
(
    Id INT
)

SET @schema_names =
(
    SELECT 
       schema_names
    FROM @schema_table
    WHERE idx = @i
)

TRUNCATE TABLE #temp

INSERT INTO #temp(Id)
EXEC('SELECT id FROM '+@schema_names+'.company;')

DECLARE my_cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT ID FROM #TEMP

OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @company_id;

edited Feb 1, 2018 at 12:42

Valerica

1,6281 gold badge14 silver badges21 bronze badges

answered Feb 1, 2018 at 12:30

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Dynamic Schema Name in Cursor

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related