user721126 user721126 - 5 days ago 6
SQL Question

TSQL Querying with Multiple Result sets

I have 3 tables where an ID may be in table 1, table 2, table 3 or in both table 1 and table 3.

What I need to do is pass the ID to the stored procedure as a parameter and then check the 3 tables and return a result set where the ID is found in 1 of the 3 tables.

In the case that the ID is found in both table 1 and table 3, I need to return the result set for table 3.

All tables DO NOT contain the same number of columns. I can't seem to get this constructed correctly.

This is my stored procedure so far:

ALTER PROCEDURE [dbo].[Login]

@userId varchar(20)

AS
BEGIN
SET NOCOUNT ON;

-- Customer Service User
IF EXISTS
(
SELECT cs.id,
first_name,
last_name,
logins.[email_address],
logins.[password],
phone,
shift,
manager,
location
FROM customer_service cs
INNER JOIN logins ON cs.id = logins.[id]
WHERE cs.id = logins.[id]
)

-- Marketing User
IF EXISTS
(
SELECT mktg.id,
first_name,
last_name,
logins.[email_address],
logins.[password],
phone,
manager,
location
FROM marketing mktg
INNER JOIN logins ON mktg.id = logins.[id]
WHERE mktg.id = logins.[id]
)

-- Remote User
IF EXISTS
(
SELECT ru.id,
first_name,
last_name,
logins.[email_address],
logins.[password],
manager,
location
FROM remote ru
INNER JOIN logins ON ru.id = logins.[id]
WHERE ru.id = logins.[id]
)

Answer

You want the stored proc to return the same columns no matter which table the data is coming from, so I would union the select statements together and use NULL for column names that aren't in a given table. Since you only want to return a record from the first table when the id doesn't exist in the third table, I added that to the first select's where clause:

ALTER PROCEDURE [dbo].[Login]

@userId varchar(20)
AS BEGIN SET NOCOUNT ON;

-- Customer Service User
SELECT
    cs.id,
    first_name,
    last_name,
    logins.[email_address],
    logins.[password],
    phone,
    shift,
    manager,
    location
FROM customer_service cs
    INNER JOIN logins ON cs.id = logins.[id]
WHERE cs.id = @userId
    and not exists(select 1
                   from remote
                   where id = @userId)
union all
-- Marketing User
SELECT
    mktg.id,
    first_name,
    last_name,
    logins.[email_address],
    logins.[password],
    phone,
    null as shift,
    manager,
    location
FROM marketing mktg
    INNER JOIN logins ON mktg.id = logins.[id]
WHERE mktg.id = @userId
union all
-- Remote User
SELECT
    ru.id,
    first_name,
    last_name,
    logins.[email_address],
    logins.[password],   
    null as phone,
    null as shift,                                 
    manager,
    location
FROM remote ru
INNER JOIN logins ON ru.id = logins.[id]
WHERE ru.id = @userId

end
Comments