is SELECT * FROM EXEC mysproc POSSIBLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

is SELECT * FROM EXEC mysproc POSSIBLE

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-03-14 : 14:02:16

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

468 Posts

Posted - 2002-03-14 : 14:14:57


/*
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

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-03-14 : 19:31:34

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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 19:35:43

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)?

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-03-15 : 12:40:47

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.

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-03-15 : 15:00:38

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

Jay99

468 Posts

Posted - 2002-03-15 : 15:14:39


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

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-03-18 : 15:54:36

thx, Jay99

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

thx again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources