asked on

T-SQL stored procedure question

Hi Experts,

I need to build a stored proc to retrieve data from a few tables. Right now I have a couple of queries that do this but I found out that they don't really do what I need. My db is SQL SERVER 2005. Here are my two tables and sample data in them:

Table A
=========================================================
processID processName
--------- ----------------------------------
2334                        middle.Athletics.6788.roberts.id
2334                        middle.Athletics.5699.roberts.id
2334                        elementary.PT.5000.shcoles.dep
2334                        elementary.PT.4663.shcoles.dep
2334                        elementary.PT.6886.shcoles.dep

Table B
=========================================================
processID processName
--------- ----------------------------------
2334                        middle.Athletics.6788.roberts.id
2334                        middle.Athletics.5699.roberts.id
2334                        elementary.PT.5000.shcoles.dep
2334                        elementary.PT.4663.shcoles.dep
2334                        elementary.PT.6886.shcoles.dep
2334                        middle.PT.3400.baylor.id

I need to get all the rows that are in table A and then the extra row in table B (last row - processName: middle.PT.3400.baylor.id).

Now, sometimes table A will be empty but table B will always have values. I need to first check if processID 2334 has any rows in table A. If it has, then retrieve all the rows from table A, then look at table B. If the rows in table B are the same as that are in table A, then return all the rows from table A. If not, then return all the rows in table A plus any additional rows in table B with the same processID. Attached is my attempt at writing a stored proc for this.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.GetAllProcesses
	@processID_in int, 
	@processName_in varchar(50)
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT tb1.processID, tb1.processName
	FROM	tableA tb1
	WHERE	tb1.processID = @processID_in
	
	--this is where I need the code to check if all the rows in tableB are returned as well
END
GO

Open in new window

Lowfatspread

then you just need a UNION

SELECT tb1.processID, tb1.processName
      FROM      tableA tb1
      WHERE      tb1.processID = @processID_in
union
SELECT tb1.processID, tb1.processName
      FROM      tableB tb1
      WHERE      tb1.processID = @processID_in

Lowfatspread

unless if tableA IS empty do you not want the table B rows either?

in which case

SELECT tb1.processID, tb1.processName
FROM tableA tb1
WHERE tb1.processID = @processID_in
union
SELECT tb1.processID, tb1.processName
FROM tableB tb1
WHERE tb1.processID = @processID_in
and exists (SELECT tb1.processID, tb1.processName
FROM tableA tb1
WHERE tb1.processID = @processID_in)

roger v

ASKER

@Lowfat:

Yes, if tableA is empty, then I need tableB rows. Essentially what I need is all the rows in tableA, plus any additional rows in tableB. I'll try out your code and see if that helps.

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

roger v

ASKER

Actually, the union won't work. There is another table involved here that has an association with just the first table (tableA). Since that 3rd table will not be available for tableB, it'll have to be two separate queries. Or a function that does a match between the 2 tables, and retrieves all the data from both tables.

roger v

ASKER

Since I didn't have that in my initial requirements, I'll go ahead and close this and open another question. Could you kindly follow up in that question?