SQLTeam.com | Weblogs | Forums

How to pass list of table names to a stored procedure

I'm trying to fetch the data from multiple tables by passing the table name but by below mentioned Query I'm getting one table at a time but I need to fetch 2 or 3 tables at a time

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

EXECUTE sp_executesql @sql

END;

--- EXEC usp_sample 'table1,table2';

If I execute this, output is displaying two times.

Tables data:

hi pass the list

in your SP sql
put the comma seperated list into individual tables and then run each seperately

example

table1,table2,table3

becomes

table1
table2
table3

now

select * from table 1
union all
select * from table 2
union all
select * from table 3

Not like this sir, I need to execute with stored procedure statement only.

Please help me.

do a replace

replace('table1,table2,table3',',',' union all select * from ')

something like this and execute the sql

This is easy to do but why are you doing this? what is the business requirement?

select concat('select top 10 * from ', c.value) as query,
        Row_Number() over (order by (select 0)) as id
	into #kabul
  from string_split(@tablenames, ',') c  

DECLARE @counter INT = 1, @rows int, @sql nvarchar(max)

	select @rows = count(1)
	 from #kabul

	if @rows > 0
	begin
		WHILE @counter <= @rows
		BEGIN
			SELECT @sql= query
			  from #kabul  
			 where id = @counter
			
			EXECUTE sp_executesql @sql

			SET @counter = @counter + 1;

			IF (@counter > @rows)
				BREAK  
			ELSE  
			CONTINUE  
		END	
	end
	
	drop table #kabul

A couple things here. The comma separated list will not work if you have different schemas. It also allows for SQL injection if not done properly. You could pass in an XML parameter that has both schema and table in it. Then, using what yosiasz provided, bump the xml against system tables to get the schema and table names. This validates the data being passed in are valid tables and helps prevent SQL Injection

select concat('select top 10 * from ', c.table_schema,'.',c.table_name) as query,
Row_Number() over (order by (select 0)) as id
into #kabul
from (openxml(xml parm)) x
join information_schema.tables c
on x.table_Schema = c.table_schema
and x.table_name = c.table_name

1 Like

or include the schema in the delimited?
'dbo.coconuts,mac.cheese'

@Vasu ,

What version of SQL Server are you using?

For businesses requirements

2014 version

I need to fetch 2 or 3 tables at a time with stored procedure.

What has been provided to you should work taking note of what @mike01 said

If I execute this code, the rows in the output are repeating 2 times as mentioned below
Uploading: IMG_20210823_154817.jpg...

Which code?

This code sir

@Vasu ,

First, it would have been real nice of you to provide some readily consumable data instead of some nearly useless pictures. You're kind of new so I'm doing for you this time. Please do similar in the future to help us help you better and more quickly. Also, on forums that have no indication of version and edition, always be sure to include both.

Here's the code to provide the readily consumable data.

--===== Example Tables and data
DROP TABLE IF EXISTS #Table1,#Table2,#Table3
;
 SELECT *
   INTO #Table1
   FROM (VALUES
         ( 1, 2, 4)
        ,(67,34,23)
        )v(A,B,C)
;
 SELECT *
   INTO #Table2
   FROM (VALUES
         (11,22,14)
        ,( 6, 7,45)
        )v(A,B,C)
;
 SELECT *
   INTO #Table3
   FROM (VALUES
         (99,98,97)
        ,(96,95,94)
        )v(A,B,C)
;

Then do this... You can convert it stored procedure... you have t have some of the fun. :smiley:
The code uses an iTVF (inline table valued function) that you can get from the "Resources" at the bottom of the following article.

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

--===== This is what would go into your stored procedure.
     -- The first variable would be a parameter that you passed'
DECLARE @TableCSV VARCHAR(8000) = '#Table1,#Table2,#Table3'
;
--===== This would be a local variable in the stored procedure.
DECLARE @SQL VARCHAR(8000)
;
--===== This would check for and regject SQL Injection Attempts
     -- If anything other than normal table name characters are detected,
     -- eit silently so we don't give an attacker any hints.
     IF @TableCSV LIKE '%[^-__ .,a-zA-Z0-9#[[]]]%' 
BEGIN
PRINT 'wtf';
RETURN
END;
--===== Split the table names and create the dyamic SQL from those.
 SELECT @SQL = ISNULL(@SQL+' UNION ALL'+CHAR(10),'')+' SELECT * FROM '+Item
   FROM dbo.DelimitedSplit8K(@TableCSV,',')
;
--===== Print and execute the dynamic SQL.  Comment out the PRINT if you want.
  PRINT @SQL;
   EXEC (@SQL)
;

And that creates the following output.
image

Try it with just one or two of the table names and you'll see that it works as expected.

1 Like

I love this part

BWAAA-HAAAAA!!1... lordy. I meant to remove the PRINT and just leave the RETURN. Looking back on it,though, it might be appropriate considering that it's in response to a possible SQL INJECTION attack.

I did blow coffee out my nose when I saw your post. I couldn't believe I forgot to take that out. Good lesson on what not to do during temporary code used for development. :smiley:

2 Likes

@harishgg1 ,
That's not a bad idea but can you publish the actual code instead of just an image? Also, if you want all the data to be returned in a single result set, what would you do then?

And that does bring up a good point. The Op didn't actually state if he wanted a single return or 1 return per table.

And, while we're at it, I won't use a WHILE Loop or XML for such a thing no matter how small the number of rows might be. It's slower than the DelimitedSplit8K function and it always requires more code. It's a bad habit to get into.

Also, the code I posted should work find when using the 2 part naming convention (schemaname.tablename) but it will not work if you've used "inconvenient" table names that have spaces or other characters in them that would require to table name to be encased in brackets. IMHO, there's no excuse for introducing such morbidity into object names.