union of multiple procedures - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

union of multiple procedures

Author

Topic

cs01rsw
Starting Member

14 Posts

Posted - 2007-07-19 : 11:35:11

Hi, i have encountered a problem in which i am sure has a simple answer. I am used to MySQL and am new to SQL Server and would appreciate any expert advice. Thanks

I have 4 stored procedures and need to get the data that they generate out of all of them and place them into one resulting table.

here is the code i had for it:

USE [SQLexport]
GO
/****** Object: StoredProcedure [dbo].[AllSpirometry] Script Date: 05/31/2007 17:05:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[AllSpirometry] AS
BEGIN
select * from dbo.All05
UNION
select * from dbo.All075
UNION
select * from dbo.All01over6
UNION
select * from dbo.All075under6
END

and here are the errors that occurred:

Msg 208, Level 16, State 3, Procedure AllSpirometry, Line 3
Invalid object name 'dbo.All05'.
Msg 208, Level 16, State 3, Procedure AllSpirometry, Line 3
Invalid object name 'dbo.All075'.
Msg 208, Level 16, State 3, Procedure AllSpirometry, Line 3
Invalid object name 'dbo.All01over6'.

I have been told that this code will not work in SQL Server and been given a longggggg piece of code that will make it work by placing all data into a temporary table. I am adamant that such a long piece of code is not needed, surely there must be a way to get this to work very simply in SQL Server.

I would appreciate any help you can provide me, thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 12:34:21

Yes you would need to create a temp table and do the inserts into it from each of the procs. I dont think its "a long piece of code". should be fairly simple..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cs01rsw
Starting Member

14 Posts

Posted - 2007-07-19 : 12:39:14

hi, thanks for reply. sorry to ask this but do you have any sample code you could send me to enable me to do this. i am sure like you said it is very quick and i am a bit stuck as to what to do and the code i have been given is so long that it doesn't make sense to me :(

thanks for much for your help

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 13:47:28

Create a temp table with the structure same as your dataset returned by the proc.

 INSERT INTO #Temp1 EXEC dbo.YourProc

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cs01rsw
Starting Member

14 Posts

Posted - 2007-07-19 : 14:04:49

lol i knew it would be something simple just couldn't get my head round it. really appreciate your help. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources