Stored Procedure that joins tables and accepts a parameter to filter the result

Question

Post reply

Stored Procedure that joins tables and accepts a parameter to filter the result

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Data Rat SSC Eights! Points: 943 More actions · Answer 1

Thanks for your Help... Greatly Appreciated !!!!!

Attachments:

You must be logged in to view attached files.

Data Rat SSC Eights! Points: 943 More actions · Answer 2

Thanks So Much For Your Help. I was wondering what will this stored procedure return if I the SSN doesn't exist in any table. Also I was wondering what if I wanted a column for the Table Name is that possible? Thanks !!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

So, from the output in the file, it appears that more than one SSN can be input to the stored procedure. That contradicts everything you have said in this post and others. So what are you trying to get in the end?

Data Rat SSC Eights! Points: 943 More actions · Answer 4

I'm sorry that was a mistake. I was just focused on showing the multiple rows. But yes there will only be one ssn per result set. Again I greatly apologize.

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

Data Rat wrote:

Thanks So Much For Your Help. I was wondering what will this stored procedure return if I the SSN doesn't exist in any table. Also I was wondering what if I wanted a column for the Table Name is that possible? Thanks !!!

The solution proposed by Jonathan Roberts gives you the table name in its own column. Is there something about this solution which you do not like?

What do you want to be returned if the SSN is not found in any table?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Data Rat SSC Eights! Points: 943 More actions · Answer 6

Thanks for the great example. What if I wanted to display the SSN's with the Table Names ?

Phil Parkin SSC Guru Points: 246987 More actions · Answer 7

Data Rat wrote:

Thanks for the great example. What if I wanted to display the SSN's with the Table Names ?

Did you even read my answer?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 8

Data Rat wrote:

What if I wanted to display the SSN's with the Table Names ?

CREATE TABLE #temp(SSN varchar(20),TableName sysname);

INSERT INTO #temp(SSN,TableName)
SELECT @SSN, 'dbo.MyTable1' 
 WHERE EXISTS(SELECT *
                FROM dbo.MyTable1 x
               WHERE x.SSN = @SSN)
...

Data Rat SSC Eights! Points: 943 More actions · Answer 9

Data Rat

SSC Eights!

Points: 943

April 14, 2020 at 10:11 am

#3742659

Thanks Sir !!! I could display "No Records Found"

Phil Parkin SSC Guru Points: 246987 More actions · Answer 10

Data Rat wrote:

Thanks Sir !!! I could display "No Records Found"

Borrowing from the script posted earlier, you can do this by adding an extra code block near the end:

/* Put an insert for each table */
INSERT INTO #temp
(
    TableName
)
SELECT 'dbo.MyTable35'
WHERE EXISTS
(
    SELECT * FROM dbo.MyTable35 x WHERE x.SSN = @SSN
);

--Handle the case where there are zero rows in the temp table
IF NOT EXISTS (SELECT * FROM #temp t)
    INSERT #temp
    (
        TableName
    )
    VALUES
    ('No results found');

SELECT *
FROM #temp;

(Note also that SQL Server tables contain 'rows' of data, not 'records'.)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kaj SSCommitted Points: 1607 More actions · Answer 11

I shouldnt have a problem joining the tables but my main concern was when i get to the end and write the where filter clause do I have to ask where the SSN number is in all of the tables. Thanks !!!

I'm confused. Do you intend to join the individual datasets in your c# mvc code using linq or something, or are you asking a rookie question about how to do joins in T-SQL?

If it's the latter, then we normally specify join conditions in the ON section:

SELECT a.SSN, a.someOtherColumn, b.someColumn, c.someOtherColumn
FROM dbo.FirstTable AS a
INNER JOIN dbo.SecondTable AS b ON b.SSN=a.SSN
LEFT JOIN dbo.ThirdTable AS c ON c.SSN=a.SSN
WHERE a.SSN = @pSSN

As you can see, the WHERE clause only contain one SSN column reference, since the joins take care of the other row selection criterias with the same SSN from the other joined tables.

This reply was modified 4 years, 8 months ago by kaj.

Phil Parkin SSC Guru Points: 246987 More actions · Answer 12

kaj wrote:

I shouldnt have a problem joining the tables but my main concern was when i get to the end and write the where filter clause do I have to ask where the SSN number is in all of the tables. Thanks !!!
I'm confused. Do you intend to join the individual datasets in your c# mvc code using linq or something, or are you asking a rookie question about how to do joins in T-SQL?
If it's the latter, then we normally specify join conditions in the ON section:
SELECT a.SSN, a.someOtherColumn, b.someColumn, c.someOtherColumn
FROM dbo.FirstTable AS a
INNER JOIN dbo.SecondTable AS b ON b.SSN=a.SSN
LEFT JOIN dbo.ThirdTable AS c ON c.SSN=a.SSN
WHERE a.SSN = @pSSN
As you can see, the WHERE clause only contain one SSN column reference, since the joins take care of the other row selection criterias with the same SSN from the other joined tables.

This would return NULL if the SSN existed only in the third table (for example). Not what the OP requires, I believe.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kaj SSCommitted Points: 1607 More actions · Answer 13

This would return NULL if the SSN existed only in the third table (for example). Not what the OP requires, I believe.

Yes, well that's the question, isn't it? I wouldn't presume to know that (even after all that is said and done).

Because we don't know anything about the data, I just made up a simple demo query. The OP would have to come up with his own query based on his knowledge of the data. One of the tables is most likely a SSN master table. If we call that table FirstTable, I'm home free (maybe). 🙂

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 14

@DataRat and to all those helping him... please raise your hand if you wouldn't be upset if it was YOUR plain text SSN that was splattered across 35 tables even on a supposedly "in-house only" database. 🙁 This is just wrong and so is helping someone that has such a thing. You're just helping someone perpetuate the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kaj SSCommitted Points: 1607 More actions · Answer 15

I don't know that we know that it's Social Security Numbers in clear text, do we? We only know that the name of the column is SSN, I think. It could theoretically contain anything. 🙂 Including a properly scrambled Social Security Number.

But, sure, it would be a very bad design if it does indeed use clear-text SSN information as primary and foreign key!