can a sql store procedure return more than one table.

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL-Server

Hi,

can a sql store procedure return more than one table .

how?

Thanks

Posted 21-Jul-11 0:03am

manika123

Add a Solution

3 solutions

Solution 2

Yes.

CREATE PROCEDURE spSomeStoredProcedure AS 
BEGIN

Select * from table1

Select * from table2

Select * from table3

END
GO

and check this also.

http://shan-tech.blogspot.com/2005/08/sql-server-stored-procedures-returning.html[^]

Posted 21-Jul-11 0:14am

Toniyo Jackson

v2

Solution 3

yes the above ans are correct ... but technically speaking they don't return the tables ..... as an output ..... since because they don't have there return type as a Table ..... but they process the query and give the table as an output .... which if you want to capture .... you an use the ADO .NET and retrieve all the tables processed by it by the code that they have given ......

Posted 21-Jul-11 5:40am

Pravin Kumar Dubey

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

@nuraGGupta@ · Accepted Answer · 2011-07-21T00:13:00

Solution 1

yes, it can

create procedure sp_return2tables
As
Select * from table1
Select * from table2
GO

It will do, on the front-end you can fetch the values from dataset1 and dataset2.
I hope this will solve your requirement.

Anurag

Posted 21-Jul-11 0:13am

@nuraGGupta@

Comments

manika123 21-Jul-11 6:20am

I agreed.
but if i have to return 1 lacs table then ?(actually one interviewer asked me that ques.)

@nuraGGupta@ 21-Jul-11 7:38am

This command can return you the list of all the tables in a database:
SELECT * FROM sysobjects WHERE xtype='U'
use a while loop or a cursor to iterate through each table name and return the values from them.

@nuraGGupta@ 21-Jul-11 8:53am

You can click on "Mark as Solution" button if you found the answer useful. [;)]

Uday P.Singh 21-Jul-11 12:46pm

my 5!

@nuraGGupta@ 21-Jul-11 14:38pm

Thanks Uday

manika123 22-Jul-11 2:04am

Thanks Anurag...