Solved

sql 2000 - can I use union with stored procedures?

Posted on 2006-11-28
6
3,898 Views
Last Modified: 2008-01-09
I am using SQL 2000
I have a very lenght sql query with many select queries joined with UNION, like this:
---------------------------------------------------------------------------------------------------
select
Col1, Col2, COl3, Col4
form MyTable1
uion
select
Col1, null, Col2, Col3
from MyTable2
...
....
-----------------------------------------------------------------------------
If I create stored procedures as follows:

Create procedures as sp1
select
Col1, Col2, COl3
form MyTable1

create procedures as sp2
select
Col1, Col2, COl3
form MyTable2

-----------------------------------------------------------------------------
I would then like to do something like this
select * from
(exec sp1) as T1
union
select * from
(exec sp2) as T2
-------------------------------------------------------------------------------
I tried the above and It doesn't work (syntax errors): Incorrect syntax near the keyword 'execute'.
Is it possible to use UNION when executing stored procedures?

0
Comment
Question by:novice12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Expert Comment

by:gpompe
ID: 18031146
you cannot do that
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18031150
no, you cannot use UNION with stored procedures.

but you CAN use UNION with stored functions, so if you can transform your procedures into functions, you can do this:

select * from dbo.Function1()
union all
select * from dbo.Function2()


note that you should use UNION ALL wherever possible, as UNION alone does a implicit DISTINCT on the returned rows, which first of all costs resources and is often giving confusion
0
 

Author Comment

by:novice12
ID: 18031264
How can I transform a select query into a function?

create function1()
as
 begin
  select * from table1
 end

Is the above possible?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18031284
now, if you are only doing a select from a table without any parameters, you will even better be off to use a view instead of procedures/functions.

create view view_1
as
 begin
  select * from table1
 end


now, the function way would be:


create function function1()
returns table ( col1 int, col2 varchar(20)  )
as
 begin
  return ( select field1, field2 from table1)
 end


with a paramter:

create function function1( @param int )
returns table ( col1 int, col2 varchar(20)  )
as
 begin
  return ( select field1, field2 from table1 where somefield = @param)
 end


0
 

Author Comment

by:novice12
ID: 18031329
I will go with the view. It's much easier. Thanks
0
 

Expert Comment

by:laparico2002
ID: 20470706
I have a stored procedure as written below;
CREATE PROCEDURE StaffnFamily
AS
SELECT EmployeeId, Name, DateOfBirth, RelationShip
FROM FamilyDetails union
 ( SELECT EmployeeId, Name, DateOfBirth, 'Self' AS "_'Self'_"
FROM Employees )
GO

But when i called it in my vb 60 program, it give me an error; "Invalid object name 'StaffnFamily'.
can this stored procedure be converted in view for me to use? if so how?
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question