1

I have one stored dbo.usp_GetCustInfo '1','Some Parameter' which return two tables

As

Table 1

DocID    DocName

Table 2

CustID     CustName

I want to store this into temp table. i can use any of the statement Insert into or Select * Into

As pointed in this answer Insert results of a stored procedure into a temporary table it works only if return one table

Community
  • 1
  • 1
Shaggy
  • 5,422
  • 28
  • 98
  • 163

1 Answers1

-1

This sounds like a job for OUTPUT parameters within your stored procedure.

http://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

I don't know of any possibility to handle plural result-sets within T-SQL, as far as i know it's strictly designed to handle only singular results - and since you can't edit the Stored Procedure itself, you should work with the result within the language you are using to process the information from SQL. I can provide an example for ColdFusion, i guess there are similar approaches possible for most other languages.

<!--- This calls the procedure --->
<cfstoredproc procedure="usp_GetCustInfo" datasource="your_ds">
  <cfprocparam cfsqltype="cf_sql_varchar" value="1" />
  <cfprocparam cfsqltype="cf_sql_varchar" value="Some Parameter" />
  <cfprocresult name="local.table_1" resultset=1 />
  <cfprocresult name="local.table_2" resultset=2 />
</cfstoredproc>

<cfdump var=#local.table_1# title="Result Table 1">
<cfdump var=#local.table_2# title="Result Table 2">
wiesion
  • 2,349
  • 12
  • 21
  • Everyone can link to docs. Try to provide a minimal example to make your anser more informative. – Kris Jul 09 '14 at 12:07
  • @Weision i can not change existing stored procedure structure ! – Shaggy Jul 10 '14 at 05:38
  • @SagarDumbre Which language are you using to communicate with SQL Server? In some (i guess actually in most) languages it's possible to handle multiple results from a query. I can provide an example for ColdFusion, will add it to my post – wiesion Jul 10 '14 at 07:41