Stored Procedure help

Question

Stored Procedure help

Sasha H

Valued Member

Points: 58
More actions
September 20, 2005 at 9:02 am

#102807

Hello,
Trying to create a general stored procedure that does the following:
By entering a persons first and last name you should be able to find the address. The address should be located via a person ID.
Has two parameters (first and last name).

Thanks all

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 really help without the structure of the tables... also whta makes you think that the first and last name combinaison is unique across all the system (to match to a unique id)???

Ray M SSC-Insane Points: 21093 More actions · Answer 2

What exactly are you looking for?

Sql server books online is quite extensive, and should be able to answer alot of your questions.

From Books online

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_7yw5.asp

Here's some more light reading

http://www.sql-server-performance.com/tn_stored_procedures.asp

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

Does this seem right?

CREATE PROCEDURE find_person_address

@LAST_NM varchar(40),

@FIRST_NM varchar(40),

@ADDRESS_TXT

AS

SET @PERSON_ID = (SELECT address FROM PERSONS

WHERE @LAST_NM=@LAST_NM

AND FIRST_NM=@FIRST_NM)

RETURN @ADDRESS_TXT

GO

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

No because first + last name IS NOT A KEY. You'll have to list all possible adress for that name, then they'll have to pick the right one. Or you have them ask for a phone number/zipcode/ssn that is UNIQUE and code it about like you did.

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 5

Well, right direction I think. kinda 🙂

First things first. You probably really want to

select address

from persons

where last_nm = @last_nm

and first_nm = @first_nm

And then you want to actually return the address, so you have to assign that value to your output parameter:

select @address_txt = address

from persons

where last_nm = @last_nm

and first_nm = @first_nm

That should get you closer ...

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

Sasha H

Valued Member

Points: 58

September 20, 2005 at 11:59 am

#591460

Thanks all, hopefully I'll get there

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 7

rgr'us concerns are also valid -- take a look at your overall design, too, and be sure that this idea will really work. What are the odds, for example, that the "searcher" (your application?) will guess the correct first and last names, with the right spelling?

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

Your right guy's, but I'm just generally trying to put something together in theory, this won't actaully be a live procedure, just a general one.

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

How does this look so far,

CREATE PROCEDURE find_person_address

@LAST_NM varchar(40),

@FIRST_NM varchar(40),

AS

SET @LAST_NM AND @FIRST_NM = (SELECT person_id FROM PERSONS

WHERE @LAST_NM=@LAST_NM AND @FIRST_NM=@FIRST_NM)

select @PERSON_ID = address

FROM PERSONS

where last_nm = @last_nm

and first_nm = @first_nm

GO

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

SET @Adress = (Select Adress from dbo.Adresses A inner join dbo.Persons P ON A.PersonID = P.PersonID where P.FName = 'RG' and P.LName = 'US').

That'll throw an error if you have more than 1 adress for that person (desired behavior).

use an output param to send the results.