April 13, 2020 at 4:47 pm
I have 35 tables all with a SSN column. I need to use a stored procedure and write a Select statement in this stored procedure joining all of these tables so that the stored procedure will return to me rows were it found the @SSN Parameter in one or more of the tables I am passing to the stored procedure . I was wondering if someone could assist me in what the Select statement that will join all of these tables should look like and the WHERE CLAUSE that I am filtering the results on based on the @SSN parameter passed to the stored procedure. Thanks !!!
April 13, 2020 at 4:50 pm
What are the parameters for the proc? Table name and SSN? Or just SSN?
Why do you think that the tables all need to be joined together?
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
April 13, 2020 at 4:59 pm
I am not sure why you keep opening new threads for basically the same question other than you don't seem to be be getting the answer you want. So let us start over on this thread with one simple and basic question:
If the following is how you invoke the stored procedure, what is your expected results?
exec dbo.SearchSSN @ssn = '555-55-5555';
April 13, 2020 at 5:06 pm
I apologize, but I have experimented with so many different queries or ways to solve this problem and I was unsuccessful. Most of my experience is in .Net Development and I am fairly new to database development or trying to solve problems of this nature with SQL because the tables are not related they just all have an SSN column in common. But to answer your question given that I am passing in a SSN value for the Parameter I would want to see if that SSN value exits in all of my tables and return the Rows of the tables that contain the SSN I passed in . Thanks for your help I Greatly Appreciate it.
April 13, 2020 at 5:33 pm
But to answer your question ..
You do realise that you have not done this, don't you?
Let's say that the SSN exists in all but one of the tables.
What should the stored proc return?
It could return some text ("SSN not found in all tables", "SSN found in 34 tables", "Failed", "You cannot be serious!", ...)
It could return a number (34) indicating the number of tables it was found in.
It could return a BIT (1 if SSN found in all tables, 0 otherwise)
And any number of other combinations ...
So please be specific and describe exactly what you want the proc to return. As you are a .NET developer, this should be a walk in the park for you.
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
April 13, 2020 at 5:46 pm
Thanks for your help and the critical thinking skills lesson, I greatly appreciate that. What I would like for the stored proc to return is all the rows (With the SSN Column) of the tables that matched the SSN that I passed in. Thank You !!!
April 13, 2020 at 6:02 pm
If it were me, I'd do something like this to identify all the tables containing the column I want...
SELECT ao.name AS objectName
, ao.type_desc
, ac.name as ColumnName
FROM sys.all_columns ac
INNER JOIN sys.all_objects ao
ON ao.object_id = ac.object_id
WHERE ac.name = 'SSN';
You could create a cursor (yeah, I should probably duck when saying that), but then you could create some dynamic SQL if you wanted to collect all the SSNs into a single table with the name of the table it exists in.
April 13, 2020 at 6:11 pm
I apologize, but I have experimented with so many different queries or ways to solve this problem and I was unsuccessful. Most of my experience is in .Net Development and I am fairly new to database development or trying to solve problems of this nature with SQL because the tables are not related they just all have an SSN column in common. But to answer your question given that I am passing in a SSN value for the Parameter I would want to see if that SSN value exits in all of my tables and return the Rows of the tables that contain the SSN I passed in . Thanks for your help I Greatly Appreciate it.
I will try again by having you pretend I am from Missouri and Show Me what the results from the call to the procedure should look like. Don't describe it, show it.
Also show what the results would like if the SSN passed in does not exist in any of the tables.
April 13, 2020 at 6:16 pm
Thanks for your help and the critical thinking skills lesson, I greatly appreciate that. What I would like for the stored proc to return is all the rows (With the SSN Column) of the tables that matched the SSN that I passed in. Thank You !!!
Presumably each of these tables contains different columns?
If so, and there are 35 hits, you'd need the proc to return 35 separate recordsets. Is that true? Also, if (say) 10 of the tables contain hits, you'd get 10 recordsets, but you would not know which tables they were from.
Have you really thought this through?
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
April 13, 2020 at 6:43 pm
Thats a good point I don't know how to include the table name in the result set. This is not easy for someone who doesn't write select statements all the time. Our D.B.A. / SQL Developer is very busy so I was asked to write the sql to give us the data and then display it in a table in my MVC .Net Web Application. But thats why I am here asking questions. 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 !!!
April 13, 2020 at 6:45 pm
CREATE PROCEDURE FindSSN(@SSN varchar(20)) AS
BEGIN
CREATE TABLE #temp(TableName sysname);
INSERT INTO #temp(TableName)
SELECT 'dbo.MyTable1'
WHERE EXISTS(SELECT *
FROM dbo.MyTable1 x
WHERE x.SSN = @SSN)
INSERT INTO #temp(TableName)
SELECT 'dbo.MyTable2'
WHERE EXISTS(SELECT *
FROM dbo.MyTable2 x
WHERE x.SSN = @SSN)
INSERT INTO #temp(TableName)
SELECT 'dbo.MyTable3'
WHERE EXISTS(SELECT *
FROM dbo.MyTable3 x
WHERE x.SSN = @SSN)
/* Put an insert for each table */
INSERT INTO #temp(TableName)
SELECT 'dbo.MyTable35'
WHERE EXISTS(SELECT *
FROM dbo.MyTable35 x
WHERE x.SSN = @SSN)
SELECT *
FROM #temp
END
GO
You could try UNION ALL instead of separate inserts statements but you might overload the database.
April 13, 2020 at 7:11 pm
See the attached file please. Thanks for your Help !!!!
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply