SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 is SELECT * FROM EXEC mysproc POSSIBLE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gdeconto
Posting Yak Master

Canada
107 Posts

Posted - 03/14/2002 :  14:02:16  Show Profile  Reply with Quote
I was wondering if there was a way to select entries from the results returned by a stored procedure call

ie something like:

SELECT TOP 5 * FROM (EXEC mystoredprocedure param1, param2, param3)

has anyone done this?? the documentation doesn't seem to indicate that this is supported or not.

any help appreciated

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/14/2002 :  14:14:57  Show Profile  Reply with Quote

/*
drop proc test
go
*/
/*
create proc test
as
set nocount on
declare @i int
create table #i (i int)
set @i = 0
while @i < 100
begin
insert #i values(@i)
set @i = @i + 1
end

select * from #i
drop table #i
go
*/

create table #myi (i int)

insert #myi (i) exec test

select top 5 * from #myi
drop table #myi


now my turn . . . why do you want to do this?

Jay
Go to Top of Page

gdeconto
Posting Yak Master

Canada
107 Posts

Posted - 03/14/2002 :  19:31:34  Show Profile  Reply with Quote
Thx, Jay99.

Ahhhhhhhh, grasshopper, one must never stop asking questions ...
(something someone on the old TV series Kung Fu might have said).

Actually, I am creating an online reporting system for our users that allows them to create a custom report using any database, table and view available to him her (within limits, of course).

1. they select a database (from an intranet webpage) from a list I create using EXEC sp_databases

2. based on this selection, I then allow them to select any table or view in that database (via a stored procedure that queries the databases sysobjects)

3. the user can then select any field to display, sort by, summarize by, etc. Its a pretty cool little app.

I asked about the SELECT * FROM EXEC mysproc because I wanted to be able to offer my users the ability to select stored procedures as well as views and tables. Many fields need to be massaged a bit before display or sometimes cannot be viewed thru a view (ie a linkedserver that references an excel spreadsheet).

I had used your technique when I did selects from a linkedserver to an excel spreadsheet, but wasnt able to find out if there was a direct method that didnt involve using a temp table.

Many thanks.


Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 03/14/2002 :  19:35:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
I asked about the SELECT * FROM EXEC mysproc because I wanted to be able to offer my users the ability to select stored procedures as well as views and tables


What if the end user selects a stored procedure that doesn't return rows (a backup sproc, for example)?

Go to Top of Page

gdeconto
Posting Yak Master

Canada
107 Posts

Posted - 03/15/2002 :  12:40:47  Show Profile  Reply with Quote
well, thats where the "any database, table and view available to him her (within limits, of course)" comes in.

I prefilter the list to remove items that should not be seen or would be of little use (ie tables contain user info, system tables or databases like sysindexes or tempdb, sprocs that return no values, etc). the number of items that match this criteria are finite and manageable for me.

with the filtering and sorting tools I give them, is is an easy way to give my users access to the data they need without having to give them sql access or create endless custom reports or use some expensive third-party reporting tool ...

like most developer initiated programs, it may not be pretty but it does the job.


Go to Top of Page

ThreePea
Yak Posting Veteran

USA
83 Posts

Posted - 03/15/2002 :  15:00:38  Show Profile  Visit ThreePea's Homepage  Reply with Quote
quote:

now my turn . . . why do you want to do this?

Jay



Oh, man. I wish I could do this all the time.

For instance, I have one app that reports index fragmentation. The user selects which table they want to view, and the stored procedure does a DBCC SHOWCONTIG on that table. But SHOWCONTIG gives a ton of information, and I want to pare it down, massage it, and give the user different sort options. So I have to put the result into a temp table first. However, since SHOWCONTIG returns 21 columns, my temp table must be created with 21 columns, even though I only need three:


CREATE TABLE #frag( ... define 21 columns here ...)

INSERT INTO #frag
(... list all 21 columns here again ...)
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')


Since you can use EXEC with an INSERT, it would make this task gobs easier if you could use EXEC with a SELECT:

SELECT Rows, ScanDensity, LogicalFragmentation FROM EXEC('DBCC...')


Make sense?

p.s. "gobs" is a highly technical term meaning "more than you can imagine."


=======================================================
"The world has achieved brilliance without conscience.
Ours is a world of nuclear giants and ethical infants."
-- General Omar N. Bradley (1893-1981)

Edited by - ThreePea on 03/15/2002 15:08:55
Go to Top of Page

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/15/2002 :  15:14:39  Show Profile  Reply with Quote

use mydatabase
create procedure sp_onetwothree as
select 1 as '1', 2 as '2', 3 as '3'
go
SELECT a.[1], a.[2]
FROM OPENROWSET('SQLOLEDB','myserver';'sa';'mysapass',
'exec mydatabase.dbo.sp_onetwothree') AS a
GO


I woulda used the showcontig but sql 7 doesn't have the WITH TABLERESULTS flag ...



Jay
Go to Top of Page

gdeconto
Posting Yak Master

Canada
107 Posts

Posted - 03/18/2002 :  15:54:36  Show Profile  Reply with Quote
thx, Jay99

that was sort of what I was looking for. very nicely done

thx again.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000