October 25, 2013 at 12:07 am
1.I have one procedure that return two result set two table like
create procedure [dbo].proc1]
as
begin
select * from table1
select * from table2
end
*I dont know the table structures that the procedure proc1 returning
both table1 and table2 have dif structures i dont have access to proc1
i cant change it
2. I want to retrieve these results sets and store into a separate tables
please reply
thanks
October 25, 2013 at 12:18 am
Without change the SP, you cannot port those tables into different tables. Or ge the readonly crediential for that DB and get the table details.
October 25, 2013 at 12:24 am
ya now I know the table structure of two table returning
they are dif from each other
i want to move each of them to sepatrate tables
how ??//
October 25, 2013 at 12:45 am
You cannot capture more than 1 result set into different tables tables.
October 25, 2013 at 12:58 am
you can get table structure from procedure.
use this to get column from SP table
;WITH SP AS (
SELECT
o.name AS proc_name, oo.name AS table_name, oo.id, ooo.name,
ROW_NUMBER() OVER(partition by o.name,oo.name,oo.id, ooo.name ORDER BY o.name,oo.name,oo.id, ooo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
inner join syscolumns ooo on oo.id = ooo.id
WHERE o.xtype = 'P' and o.name='proc1')
SELECT distinct proc_name, table_name, id, name FROM SP
ORDER BY proc_name,table_name
You want to get table in t-sql or in some program language?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply