Conditional Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conditional Stored Procedures

Author

Topic

coleman
Starting Member

2 Posts

Posted - 2002-01-30 : 08:50:42

I am trying to write a stored procedure that use a table that contains serveral variations of a person's name. I need the stored the procedure to chose 1 out of 2 possible name types for the mailing name and 1 out of 5 possible name types for the salutation. The table is multiple record table.

Please advise.

Nazim
A custom title

1408 Posts

Posted - 2002-01-30 : 10:19:31

your question is too vague.

post your table schema , sample data and the expected output. we will try to help you.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

coleman
Starting Member

2 Posts

Posted - 2002-01-30 : 10:39:23

The table is called name

Fields: nameid,nametitle,namefirst,namemiddle,namelast,namesuffix,
nameformn,namesalut,nametype

Each nametype is its own record in the table.
For using a mailing name I look at 2 types combined or preferred. I want the combine if one exists if one does not exist I want the preferred.

For the salutation I want to evaluated the following name types: Special1, Special2, Combined, Formal and informal.

Sample output should be:
If combined with salutation of Special2
nameid nametitle namefirst namemiddle namelast namesuffix nameformn
1 Mr. and Mrs. John M. Doe Jr. Mr. and Mrs. John M. Doe, Jr.
namesalut
John and Mary

I hope this clears up what I am trying to accomplish

My goal is to create this so I can use it a view for report writing.

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-01-30 : 14:08:53

I think you want to use the "isnull" and "coalesce" functions. BOL has excellent examples. Isnull substitutes expression1 with expression2 if 1 is null. Coalesce returns the first non-null argument.

select
mailing=isnull(combined,preferred),
salutation=coalesce(special1,special2,...,'ERROR:all args are null')

- Lou

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources