SQL Server: how to execute query with cursor inside stored procedure and how to pass a list of params to the stored procedures

Question

I am trying to write a stored procedure in SQL Server which will:

• Take a list of integers as input ( let's assume these integers are "profile_id")
• pick up all the table names which has a column named as "profile_id" into a cursor
• loop through the cursor and print the profile_id value when it matches one of them in the input list of params.

Now the problem is: I am executing the procedure like this:

EXEC dbo.de_dup '1234,2345';

and getting a syntax error when trying to execute the commented out line below (Please see the procedure):

set @id = (select profile_id from @tname where profile_id in @a_profile_id );

Questions:

1. What would be the right way of executing and setting the value inside a cursor?
2. What is way (in our case) to pass a list of integers to this procedure?

This is my procedure:

ALTER PROCEDURE dbo.de_dup
    (@a_profile_id nvarchar(MAX)) 
AS
    DECLARE @tname VARCHAR(max),
            @id int;

    DECLARE tables_cursor CURSOR FOR 
        SELECT 
            a.TABLE_CATALOG +'.'+a.TABLE_SCHEMA + '.'+ a.TABLE_NAME AS table_name
        FROM 
            JobApp.INFORMATION_SCHEMA.COLUMNS a
        LEFT OUTER JOIN 
            JobApp.INFORMATION_SCHEMA.VIEWS b ON a.TABLE_CATALOG = b.TABLE_CATALOG
                                              AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
                                              AND a.TABLE_NAME = b.TABLE_NAME
        WHERE 
            a.COLUMN_NAME = 'profile_id'
        GROUP BY 
            a.TABLE_CATALOG, a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME;

    OPEN tables_cursor;

    FETCH NEXT FROM tables_cursor INTO @tname;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @a_profile_id ;
        PRINT @tname ;
        --set @id= (select profile_id from @tname where profile_id in @a_profile_id );
        --PRINT 'id : ' + @id;

        FETCH NEXT FROM tables_cursor INTO @tname;
    END;

    CLOSE tables_cursor;
    DEALLOCATE tables_cursor;
GO;

Please let me know should I provide more clarification. Thanks in advance.

Answer 1

This solution is using the dynamic SQL, As per my knowledge we need to use the dynamic SQL if we have the table name in a variable.

DBFIDDLE working code

Query:

CREATE PROCEDURE dbo.de_dup (@a_profile_id NVARCHAR(MAX))
AS
BEGIN
    DECLARE @tname VARCHAR(max)
        ,@id INT
        ,@dynamicSQL NVARCHAR(MAX);
    DECLARE @matched_tables TABLE (Name NVARCHAR(255));
    DECLARE @matched_profileIds TABLE (profile_id INT);
    DECLARE @profile_ids NVARCHAR(MAX) = @a_profile_id

    INSERT INTO @matched_tables
    SELECT DISTINCT a.TABLE_SCHEMA + '.' + a.TABLE_NAME AS table_name
    FROM INFORMATION_SCHEMA.COLUMNS a
    WHERE a.COLUMN_NAME = 'profile_id'

    WHILE EXISTS (
            SELECT 1
            FROM @matched_tables
            )
    BEGIN
        SELECT TOP 1 @tname = [Name]
        FROM @matched_tables

        SET @dynamicSQL = CONCAT (
                'select profile_id from '
                ,@tname
                ,' WHERE '
                ,''','
                ,@profile_ids
                ,','''
                ,' LIKE '
                ,'''%,'
                ,''''
                ,' + CAST(profile_id AS NVARCHAR(MAX)) + '
                ,''',%'
                ,''''
                )

        PRINT @dynamicSQL;

        INSERT INTO @matched_profileIds
        EXEC (@dynamicSQL)

        DELETE
        FROM @matched_tables
        WHERE [Name] = @tname
    END

    SELECT *
    FROM @matched_profileIds
END

Dynamic SQL that gets formed is

SELECT profile_id FROM dbo.TestTable WHERE ',123,456,789,1011,1213,' LIKE '%,' + CAST(profile_id AS NVARCHAR(MAX)) + ',%'