Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T-SQL stored procedure question

Posted on 2011-09-09
6
Medium Priority
?
297 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

604 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