How to pass list of table names to a stored procedure in SQL Server?

Question

I'm trying to fetch the data in a specific table name by passing tableName as a parameter to the stored procedure.

CREATE PROCEDURE schemaName.spDynamicTableName
    @tableName NVARCHAR(100)
AS
BEGIN
    DECLARE @sql nvarchar(max)
    SET @sql = 'SELECT * FROM ' + @tableName

    EXECUTE sp_executesql @sql
END;

--> EXEC schemaName.spDynamicTableName  'Employee';

Now, how can I pass list of table names to a procedure so that procedure will iterate over the list of table names and fetch the data from all the tables?

Answer 1

There are two ways you can do this: use a string that contains the names you want and are separated by a special character as:

Table1, Table2, Table3

and split it in the stored procedure (check this)

The second method: make a typo as follows:

CREATE TYPE [dbo].[StringList] AS TABLE
(
    [TableName] [NVARCHAR(50)] NULL
)

Add a parameter for your stored procedure as StringList:

CREATE PROCEDURE schemaName.spDynamicTableName
    @TableNames [dbo].[StringList] READONLY,    
AS
BEGIN
    
END;

Then measure its length using the following code and make a repeat loop::

DECLARE @Counter INT
DECLARE @TableCount INT

SELECT @TableCount = Count(*), @Counter = 0 FROM @TableNames

WHILE @Counter < @TableCount
BEGIN
    SELECT @TableName = Name 
    FROM @TableNames
    ORDER BY Name
        OFFSET @Counter ROWS FETCH NEXT 1 ROWS ONLY

    SET @sql = 'SELECT * FROM ' + @TableName

    EXECUTE sp_executesql @sql

    SET @Counter = @Counter + 1
END

Answer 2

Ok, let's start off with the problems you have in your current set up. Firstly it sounds like you have a design flaw here. Most likely you are using a table's name to infer information that should be in a column. For example perhaps you have different tables for each client. In such a scenario the client's name should be a column in a singular table. This makes querying your data significantly easier and allows for good use for key constraints as well.

Next, your procedure. This is a huge security hole. The value of your dynamic object is not sanitised nor validated meaning that someone (malicious) has almost 100 characters to mess with your instance and inject SQL into it. There are many articles out there that explain how to inject securely (including by myself), and I'm going to cover a couple of processes here.

Note that, as per my original paragraph, you likely really have a design flaw, and so that is the real solution here. We can't address that in the answers here though, as we have no details of the data you are dealing with.

Fixing the injection

Injecting Securely

The basic's of injecting a dynamic object name is to make it secure. You do that by using QUOTENAME; it both delimit identifies the object name and escapes any needed characters. For example QUOTENAME(N'MyTable') would return an nvarchar with the value [MyTable] and QUOTENAME(N'My Alias"; SELECT * FROM sys.tables','"') would return the nvarchar value "My Alias""; SELECT U FROM sys.tables".

Validating the value

You can easily validate a value by checking that the object actually exists. I prefer to do this with the sys objects, so something like this would work:

SELECT @SchemaName = s.[name],
       @TableName = t.[name]
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.[name] = @Schema --This is a parameter
  AND t.[name] = @Table; --This is a parameter

As a result, if the FROM returns no values, then the 2 variables in the SELECT won't have a value assigned and no SQL will be run (as {String} + NULL = NULL).

The Solution

Table Type Parameter

So, to allow for multiple tables, we need a table type parameter. I would create one with both the schema and table name in the columns, but we can default the schema name.

CREATE TYPE dbo.Objects AS table (SchemaName sysname DEFAULT N'dbo',
                                  TableName sysname); --sysname is a sysnonym for nvarchar(128) NOT NULL

And you can DECLARE and INSERT into the TYPE as follows:

DECLARE @Objects dbo.Objects;

INSERT INTO @Objects (TableName)
VALUES(N'test');

Creating the dynamic statement

Assuming you are using a supported version of SQL Server, you'll have access to STRING_AGG; this removes any kind of looping from the procedure, which is great for performance. If you're using a version only in extended support, then use the "old" FOR XML PATH method.

This means you can take the values and create a dynamic statement along the lines of the below:

SET @SQL = (SELECT STRING_AGG(N'SELECT * FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',' ')
            FROM sys.schemas s
                 JOIN sys.tables t ON s.schema_id = t.schema_id
                 JOIN @Objects O ON s.name = O.SchemaName
                                AND t.name = O.TableName);

The Stored Proecure

Putting all this together, this will give you a procedure that would look like this:

CREATE PROC schemaName.spDynamicTableName @Objects dbo.Objects AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = (SELECT STRING_AGG(N'SELECT N' + QUOTENAME(t.[name],'''') + N',* FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',@CRLF) --I also inject the table's name as a column
                FROM sys.schemas s
                     JOIN sys.tables t ON s.schema_id = t.schema_id
                     JOIN @Objects O ON s.name = O.SchemaName
                                    AND t.name = O.TableName);

    EXEC sys.sp_executesql @SQL;

END;

And then you would execute it along the lines of:

DECLARE @Objects dbo.Objects;

INSERT INTO @Objects (SchemaName,TableName)
VALUES(N'dbo',N'MyTable'),
      (N'dbo',N'AnotherTable');

EXEC schemaName.spDynamicTableName @Objects;

Answer 3

This one accepts a comma delimited list of tables and guards against SQL injection with a simple QUOTENAME escape (not sure if this is quite enough though):

IF OBJECT_ID('dbo.spDynamicTableName') IS NOT NULL DROP PROC dbo.spDynamicTableName
GO
/*
EXEC dbo.spDynamicTableName 'Students,Robert--
DROP TABLE Students'
*/

CREATE PROC dbo.spDynamicTableName
    @tableName NVARCHAR(100)
AS
BEGIN
    DECLARE @sql nvarchar(max)
    
    SELECT @sql = STRING_AGG('SELECT * FROM ' + QUOTENAME(value), ';')
    FROM STRING_SPLIT(@tableName, ',')

    --PRINT @sql
    EXEC dbo.sp_executesql @sql
END;
GO