August 30, 2004 at 1:24 pm
This may be a dumb question, but...
Can a stored procedure be used as the source for a select statement. I want to join the results of a stored procedure to another table to add the additional columns generated by the procedure. I have not been able to figure out how to select columns from a stored procedure result.
The procedure creates a cross-tab result. I may need to make it a function, but I have to call a procedure to generate the cross-tab and I can't call a procedure from within a function.
Thanks!
August 30, 2004 at 1:37 pm
Your logic is a bit backwards. You need to think of the procedure as being the master of the query. Within you procedure you can perform an infinite number of queries to insert data.
Describe the task you are trying to accomplish in a little more detail so I can outline something for you.
Ed
August 30, 2004 at 1:44 pm
I have a procedures that generates a cross-tab result like:
recordID, facilityID, CountX, Avg1, Avg2, Avg3, Total1, Total2, Total3
I have another procedure that also generates a cross-tab result like
record, facilityID, Total4, Total5, Total6
The two procedures use different tables and methods to determine the column values. I need to bring the two together. I know that in Access I can create the two xTab queries and join them on the key fields, but I need to reproduce this in SQL. I have a procedure that works fine for generating the xtab results but I need to be able to join them.
Cammy
August 30, 2004 at 2:12 pm
ok Cammy here is a pretty good outline. The basic idea is instead of attempting to pass back the data with a SELECT, it inserts it into a table seen by the master proc. The master creates the 2 needed receptacles calls the subordinate procs which populates them. Then it runs a query to join thems and return the resulting data set.
CREATE PROCEDURE MasterXRef
AS
DECLARE @XRef123 Table (recordID int NULL,.. etc)
DECLARE @XRef456 Table (recordID int NULL,.. etc)
EXEC sp_XRef123 -- change the SELECT to a SELECT INTO within procedure
EXEC sp_XRef456 -- change the SELECT to a SELECT INTO within procedure
SELECT a.recordID, a.facilityID, a.CountX, a.Avg1, a.Avg2, a.Avg3, a.Total1, a.Total2, a.Total3
b.Total4, b.Total5, b.Total6
FROM @XRef123 a
INNER JOIN sp_XRef456 b on a.recordID = b.recordID and a.facilityID = b.facilityID
August 30, 2004 at 3:51 pm
If you plan to follow the above Idea do it
THIS WAY:
CREATE PROCEDURE MasterXRef
AS
CREATE TABLE XRef123 (recordID int NULL,.. etc)
CREATE TABLE XRef456 (recordID int NULL,.. etc)
INSERT INTO XRef123 EXEC sp_XRef123
INSERT INTO XRef456 EXEC sp_XRef456
And leave your procedures alone so that you can REUSE them if you need to
then perform the above mentioned query
Just my $0.02
* Noel
August 31, 2004 at 12:14 am
You can call stored procedure by the following way.
USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'dbo.YourProcedureName') AS a
Thanks,
Ganesh
August 31, 2004 at 12:41 am
I would propose to add an optional parameter to the 2 procedures, and make them insert the results of the select in a temporary table. I would use the fact that a stored procedure can insert values in a temporary table created by the calling stored procedure. Example :
create proc tstproc
(
@mode varchar(10) = 'select'
)
as
begin
if object_id('tempdb..#tsttbl1') is null -- create temp table if not exists
begin
create table #tsttbl1 (spid smallint,kpid smallint,blocked smallint )
end
-- insert result into temp table
insert #tsttbl1
select spid,kpid,blocked from master.dbo.sysprocesses
-- if mode = select, act as before : return results of a select
if @mode = 'select' select * from #tsttbl1
end
go
create proc tstproc2 as
begin
-- create temp table for the results of tstproc
create table #tsttbl1 (spid smallint,kpid smallint,blocked smallint )
-- execute hte tstproc, ask it not to return a select
exec tstproc @mode = 'insert'
-- You can use now the results of the tstproc :
select * from #tsttbl1
-- drop not needed, as stored proc will do the drop. I allways add the drop, as SYBASE delivered a version in the past that did NOT drop the table ( it was a bug ) ...
drop table #tsttbl1
end
go
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply