Another Stored Procedure

Question

Post reply

Another Stored Procedure

Sasha H

Valued Member

Points: 58
More actions
September 26, 2005 at 12:10 pm

#89076

Trying to do the following for general purposes, here's the specifics:

Two Tables: (Person, Address)
Person: Person_ID, First_NM and Last_NM
Address: Address_ID, Person_Id (Unique identifier) and Address_Text

Two Parameters: (@First_NM, @Last_NM)

Need to return the address text by querying a person _id linked to the the person name.

TIA

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can't work, names are repeatable so you might have 2 John Smith>>returning 2 ids.

In what context would you want to do that?

Sasha H Valued Member Points: 58 More actions · Answer 2

Just an example that I need to put together, but not in actual development, here is what I have so far.

CREATE PROCEDURE sp_findaddress

@LAST_NM varchar(25),

@FIRST_NM varchar(25),

AS

SELECT @LAST_NAME, @FIRST_NM FROM PERSON

WHERE @LAST_NM, @FIRST_NM=PERSON_ID

I'm not sure how to go about querying the ADDRESS table from here??

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Seems too much like an homework question...

This is just a simple inner join, there's no way a programmer can't figure this one out.

Sasha H Valued Member Points: 58 More actions · Answer 4

Sasha H

Valued Member

Points: 58

September 26, 2005 at 1:14 pm

#593111

Anyone else?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

September 26, 2005 at 1:19 pm

#593114

http://www.w3schools.com/sql/sql_join.asp

Sasha H Valued Member Points: 58 More actions · Answer 6

Thanks RGR,

Here's what I have now:

CREATE PROCEDURE sp_findaddress

@LAST_NM varchar(25),

@FIRST_NM varchar(25),

AS

SELECT PERSON.LAST_NM AND FIRST_NM, ADDRESS.PERSON_ID

FROM PERSON, ADDRESS

WHERE PERSON.EMPLOYEE_ID=ADDRESS.ADDR.TEXT

David Webb-CDS SSCoach Points: 17398 More actions · Answer 7

This is probably closer:

SELECT PERSON.LAST_NM, PERSON.FIRST_NM, ADDRESS.ADDR_TEXT

FROM PERSON, ADDRESS

WHERE PERSON.EMPLOYEE_ID=ADDRESS.PERSON_ID

And then again, I might be wrong ...
David Webb

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

SELECT PERSON.LAST_NM, FIRST_NM, ADDRESS.Text

FROM PERSON INNER JOIN ADDRESS

ON PERSON.EMPLOYEE_ID=ADDRESS.EMPLOYEE_ID

where PERSON.LAST_NM = @LAST_NM AND FIRST_NM = @FIRST_NM

Martin Schoombee SSCoach Points: 19128 More actions · Answer 9

hehehe...definitely looks like a homework question. You'll have to buy all these okes a beer if you pass Sasha.

Sasha H Valued Member Points: 58 More actions · Answer 10

haha, unfortunately didn't pass, but give's me a better understanding of how these things work.