how to combine result of stored procedure which returns two results in SQL 2005?

Question

Hey i'm kind new in Stored procs, i have procedure like

ALTER procedure [dbo].[list_of_employee]
(
  @employee_id int
 )
 as 
begin 
select *from project_employee_rel PE
inner join employee_details E on E.employee_id=PE.employee_id
inner join project_details P on P.project_id=PE.project_id 
where PE.employee_id=@employee_id

select * from employee_details 
where employee_id=@employee_id
end

It returns two results separately I want result like 'if first query returns empty rows or null then second query get executed otherwise not Plz help me thanks in advance

The two queries have different result schemas, are you sure this is what you want? If you branch the code as you suggest, different `@employee_id`s will use widely different query plans, the structure of you results will vary depending on the value you supply ... The SP will be a performance burden instead of a performance benefit. — Jodrell, Feb 28 '13 at 11:36

score 1 · Accepted Answer · edited Feb 28 '13 at 12:13

1

Try this

ALTER procedure [dbo].[list_of_employee]
(
  @employee_id int
)
as 
BEGIN
  DECLARE @Count int
  SET @Count = select COUNT(PRIMARYKEY COLUMN) from project_employee_rel PE
    inner join employee_details E on E.employee_id=PE.employee_id
    inner join project_details P on P.project_id=PE.project_id 
    where PE.employee_id=@employee_id
  IF (@Count>0)
   BEGIN
      select *from project_employee_rel PE
       inner join employee_details E on E.employee_id=PE.employee_id
       inner join project_details P on P.project_id=PE.project_id 
       where PE.employee_id=@employee_id
   END

  ELSE
   BEGIN
       select * from employee_details 
       where employee_id=@employee_id
   END
END

edited Feb 28 '13 at 12:13

andy

5,979
2
27
49

answered Feb 28 '13 at 11:32

CodeGuru

2,722
6
36
52

hey thank u that's what i want. to improve sp performance i figured out also another solution using left outer joins – user2067120 Feb 28 '13 at 12:09
ok, thats good, i just given answer for what you want, if it is help full for you, up vote the answer. – CodeGuru Feb 28 '13 at 12:12

score 0 · Answer 2 · answered Feb 28 '13 at 11:28

0

IF Check Count of your first query
  return result set
ELSE 
  return other result set
END

answered Feb 28 '13 at 11:28

andy

5,979
2
27
49

how to combine result of stored procedure which returns two results in SQL 2005?

2 Answers2