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
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. 
The code uses an iTVF (inline table valued function) that you can get from the "Resources" at the bottom of the following article.
--===== 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.
Try it with just one or two of the table names and you'll see that it works as expected.
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.
@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.
