A View Or a Stored Procedure?

Question

Post reply

A View Or a Stored Procedure?

yuryn1961

Newbie

Points: 8
More actions
April 21, 2008 at 2:56 am

#93235

Hello,
I've got a table [CUSTOMERS] with two fields (actually more, but it doesn't matter for my question):
[CUST_NAME_EN], [CUST_NAME_RU].
[CUST_NAME_EN] is mandatory.
My task is to get a recordset with [CUST_NAME_RU] AS CUST_NAME value IF IT IS NOT NULL, BLANK OR EMPTY. Otherwise, it must be [CUST_NAME_EN] AS CUST_NAME.
Should I solve this problem with a view or a stored procedure? I'm not an SQL-programmer, so would somebody be kind to help?
Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

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

szabolcs.tamas SSC Rookie Points: 27 More actions · Answer 1

Hi!

I would solve this with a view, using case-when as following:

create view as vCustomers

as

select

case when cust_name_ru is not null or cust_name_ru <> ' ' then cust_name_ru

else cust_name_en end as Cust_Name

From customers

hope that helped!

regards

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

View should be sufficient.

You'll be needing both CASE (the the not empty string) and ISNULL (for the NULLS)

Something like this (partial query only )

SELECT ...,

CASE WHEN ISNULL(CUST_NAME_RU,'') = '' THEN CUST_NAME_EN ELSE CUST_NAME_RU END AS CUST_NAME

FROM ....

That help?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RyanRandall SSChampion Points: 13623 More actions · Answer 3

You could also consider using a computed column directly on the table...

http://www.sqlservercentral.com/articles/User-Defined+functions/complexcomputedcolumns/2397/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

yuryn1961 Newbie Points: 8 More actions · Answer 4

Thanks to everybody,

I've already found a solution (simple and elegant):

SELECT COALESCE(NULLIF(CUST_NAME_RU, ''), CUST_NAME_EN) AS CUST_NAME

FROM [CUSTOMERS]

Thanks to Peso on http://www.sqlteam.com/forums.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

yuryn1961 (4/21/2008)
SELECT COALESCE(NULLIF(CUST_NAME_RU, ''), CUST_NAME_EN) AS CUST_NAME
FROM [CUSTOMERS]

Why use Coalesce and IsNull (I assume that's what NULLIF really is)? Actually, the code above is not going to give you what you say you want. Assume CUST_NAME_RU is null. Then the ISNULL is going to return an empty string which then becomes the first argument to COALESCE. As this argument is not null, that is what COALESCE will return. So the result is going to be CUST_NAME_RU if it contains data, or the empty string '' if it is null. You will never get CUST_NAME_EN.

Try this:

select IsNull( CUST_NAME_RU, CUST_NAME_EN ) as Cust_Name

You could use Coalesce instead and that would give you the same results. However, if there is any possibility that CUST_NAME_EN could also be null, you could extend Coalesce thusly:

select Coalesce( CUST_NAME_RU, CUST_NAME_EN, 'Not Available' ) as Cust_Name

This may not apply to the situation you have described, CUST_NAME_EN is defined NOT NULL so it must contain something, but it's good to know about that feature should you ever need it.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

RyanRandall SSChampion Points: 13623 More actions · Answer 6

I assume that's what NULLIF really is

No! Look it up...

http://msdn2.microsoft.com/en-us/library/ms177562.aspx

NULLIF (expression , expression)
Returns a null value if the two specified expressions are equal.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 7

Ah, I get it. So you can get a NULL value if your string is NULL or an empty string. Cool.:cool:

I think I remember NULLIF from years ago. I could not conceive of a use for it -- the example was really contrived and I have never come across a real world situation where it was actually useful. Now I have one.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms