?
Solved

T-SQL stored procedure question

Posted on 2011-09-09
6
Medium Priority
?
295 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:roger_v
[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
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36513269
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36513280
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)
0
 
LVL 1

Author Comment

by:roger_v
ID: 36513337
@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.
0
Technology Partners: 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 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 36513617
then its just the simple union thats required...

unless you have duplicate data in each table that you still require ... since UNION  does an automatic distinct
0
 
LVL 1

Author Comment

by:roger_v
ID: 36513898
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.
0
 
LVL 1

Author Comment

by:roger_v
ID: 36513906
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?
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

764 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