Can a SP return a table? If so, how?

Is it possible for a stored procedure to return a table? If so, can you
give me an example?
TIA,
Larry WoodsTable variables can NOT be passed as parameters... However you may
1. insert rows into another table which can be used by someone else
2. insert into atable exec thestoredproc
3. select * from openquery(linkedservername, 'exec thestoredproc') as a
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.lwoods.com> wrote in message
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||--yes!!!
--example:
---
use northwind
go
create procedure dbo.spSelectTable
AS
select
EmployeeID
,LastName
,FirstName
from dbo.Employees
go
exec dbo.spSelectTable
go
Joao Mossmann
"Larry Woods" <larry@.lwoods.com> escreveu na mensagem
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||Another option could be a SQL 2000 function, which can
return a table variable... Bruce
create function dbo.funcName
(@.inputParm Varchar(20))
returns @.TableX TABLE (TableID integer,
TableLevelNo integer, TableName sysname,
PK_TableName sysname NULL)
>--Original Message--
>Is it possible for a stored procedure to return a table?
If so, can you
>give me an example?
>TIA,
>Larry Woods
>
>.
>|||Thanks to all. Good answers.
I ALWAYS get good professional answeres here!
Larry Woods
"Larry Woods" <larry@.lwoods.com> wrote in message
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||Another problem:
I have multiple records that I would like updated as a group so that I can
take advantage of the transaction capabilities of SP's. What I had
envisioned was passing a table into the SP then let the SP work on it.
Assuming that this is the CORRECT way to attack this, can I create a
temporary table, then pass the name of the table into the SP?
Does this make sense?
TIA,
Larry Woods
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eGwwDZwlDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Table variables can NOT be passed as parameters... However you may
> 1. insert rows into another table which can be used by someone else
> 2. insert into atable exec thestoredproc
> 3. select * from openquery(linkedservername, 'exec thestoredproc') as a
> Hope this helps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > Is it possible for a stored procedure to return a table? If so, can you
> > give me an example?
> >
> > TIA,
> >
> > Larry Woods
> >
> >
> >
>|||If the calling environment is just another piece of SQL code or another
stored proc, perhaps a temporary table (probably global) would do the trick?
Otherwise, a where clause for use in dynamic SQL or a list of PK values as a
string would be the other two solutions that come to mind.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e2Kz3EloDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Another problem:
> I have multiple records that I would like updated as a group so that I can
> take advantage of the transaction capabilities of SP's. What I had
> envisioned was passing a table into the SP then let the SP work on it.
> Assuming that this is the CORRECT way to attack this, can I create a
> temporary table, then pass the name of the table into the SP?
> Does this make sense?
> TIA,
> Larry Woods
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:eGwwDZwlDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Table variables can NOT be passed as parameters... However you may
> > 1. insert rows into another table which can be used by someone else
> > 2. insert into atable exec thestoredproc
> > 3. select * from openquery(linkedservername, 'exec thestoredproc') as a
> >
> > Hope this helps.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > > Is it possible for a stored procedure to return a table? If so, can
you
> > > give me an example?
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> > >
> >
> >
>