Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

T-SQL stored procedure question

Posted on 2011-09-09
6
Medium Priority
?
299 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…
Suggested Courses

569 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