Help mutliple resultsets

Transact-SQL
,
1

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 or in the case that it 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.

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]
                )
2

Two choices:

IF EXISTS
(
    SELECT *
    FROM MyTable
    WHERE Col1 = @SomeParameter
)
BEGIN
    SELECT Col1, Col2, ...
    FROM MyTable
    WHERE Col1 = @SomeParameter
END

or

DECLARE @RowCount int
SELECT Col1, Col2, ...
INTO #TEMP01
FROM MyTable
WHERE Col1 = @SomeParameter
SELECT @RowCount  = @@ROWCOUNT

IF @RowCount >= 1
BEGIN
    SELECT *
    FROM #TEMP01
END

which only runs the query once so may perform better.

1 Like
3

Thank you.