Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 11th, 2006, 04:25 AM
Authorized User
 
Join Date: Feb 2006
Location: Karur, Tamildau, India.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default selecting rows returned by executing a SP

Hi All,

Is there is any way to select the rows returned by executing a stored procedure. For example I have written a stored procedure like this

create proc ProcPublishers
as
use pubs
select * from publishers
go


and
tried to execute an statement like this to obtain the rows returned by the stored procedure.

SELECT * FROM EXEC ProcPublishers

But it is returning error. Is there is any other way to select the rows returned by procedure.

Please any one help me.

__________________
Regards,
dsekar_nat
 
Old May 11th, 2006, 05:02 AM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just

EXEC ProcPublishers.

Or

INSERT INTO #Temp
EXEC ProcPublishers


 
Old May 11th, 2006, 06:12 AM
Authorized User
 
Join Date: Feb 2006
Location: Karur, Tamildau, India.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Peso,

Thanks for replying. But still have doubt in this.

What can I do if I am using this in a view

For example

CREATE view publishers_view as
   exec ProcPublishers
    union
   some other select query


while creating this view, error gets returned.

 
Old May 11th, 2006, 06:13 AM
Authorized User
 
Join Date: May 2006
Location: Bangalore, , India.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to manudutt
Default

just as peso said , u need not to use select in sprocs , you do that with function
just type the name like -
sp_helpdb
or
exec sp_helpdb

for using temp table , u need to create it first ..



 
Old May 11th, 2006, 11:12 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

You cannot use a stored procedure as a derived table, more's the pity. Use select into as shown by Peso and use the temp table from then on.

--

Joe (Microsoft MVP - XML)




Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic number of columns returned from sp dreadjr BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 1 July 5th, 2006 03:42 PM
SP - executing error filipczako SQL Language 3 December 12th, 2005 10:50 AM
Executing a SP for each recordset of a Table anandham SQL Server 2000 1 October 19th, 2005 06:34 PM
Limit rows returned and next rows minhpx General .NET 1 August 12th, 2004 06:25 AM
SP Returned Output with Error sidneyfuerte Classic ASP Professional 0 September 8th, 2003 02:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.