Solved

T-SQL stored procedure question

Posted on 2011-09-09
6
289 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
  • 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now