November 20, 2008 at 10:59 pm
I have a SQL Server 2000 database that is poorly designed. I barely know what I am doing, this is not my normal job. I am trying to create some views for our administrative staff to use as a basis for reports.
Some of the fields have cryptic codes and other values I would like to translate. I thought I would make a select case function like:
Function Status(@StatusCode nvarchar(10)) as nvarchar(10)
case @StatusCode
where 't' then
'blah'
where 's' then
'blah blah'
else
'failed'
end
But I either cannot get the syntax correct or am making some gross error. Is a function even what I use? I know how to do all of this in crystal, but I am supposed to make some views with 'formatted data'. The original database is embarrassingly poorly designed, but we are tied to it for legacy reasons.
thanks for any help,
e
November 21, 2008 at 2:49 am
Hello,
Scalar UDFs (user defined functions) can be inefficient (Please see this article: http://sqlblogcasts.com/blogs/simons/archive/2008/11/03/TSQL-Scalar-functions-are-evil-.aspx)
You would probably be better off creating a look-up table and joining to this in your Views.
If you can’t store the look up table in the DB for whatever reason, then theoretically you could code it as a Table UDF.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 21, 2008 at 6:35 am
EK (11/20/2008)
I have a SQL Server 2000 database that is poorly designed. I barely know what I am doing, this is not my normal job. I am trying to create some views for our administrative staff to use as a basis for reports.Some of the fields have cryptic codes and other values I would like to translate. I thought I would make a select case function like:
Function Status(@StatusCode nvarchar(10)) as nvarchar(10)
case @StatusCode
where 't' then
'blah'
where 's' then
'blah blah'
else
'failed'
end
But I either cannot get the syntax correct or am making some gross error. Is a function even what I use? I know how to do all of this in crystal, but I am supposed to make some views with 'formatted data'. The original database is embarrassingly poorly designed, but we are tied to it for legacy reasons.
thanks for any help,
e
I agree with John with respect to performance and your are better off using a lookup table. Since you already have db and as a short gap you are looking towards udf your system should be
Function Status(@StatusCode nvarchar(10)) as nvarchar(10)
CASE @StatusCode
WHEN 't' THEN
'blah'
WHEN 's' THEN
'blah blah'
ELSE
'failed'
END
Thanks
Prasad Bhogadi
www.inforaise.com
November 21, 2008 at 8:24 am
I agree with John and Prasad in regard to the function. If you are creating a view or views you can put the case statement directly in the view like this:
Create View dbo.Test
AS
Select
columns,
CASE StatusCode
WHEN 't' THEN 'bah'
WHEN 's' THEN 'blah blah'
ELSE 'failed'
END as Status
From
table
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 21, 2008 at 8:58 am
Thanks all. I will add some lookup tables.
e
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply