Take a look at this piece of
junk code, what pops up in your head when you look at this?
CREATE FUNCTION [dbo].[age](@set varchar(10)) RETURNS TABLE AS BEGIN IF (@set = 'tall') SELECT * from player where height > 180 ELSE IF (@set = 'average') SELECT * from player where height >= 155 and height <=175 ELSE IF (@set = 'low') SELECT * from player where height < 155 END
If you are thinking silly you, you can’t have an IF statement like that in a function, then you have disappointed me. What you really should be saying is the following: why are you hardcoding this, create a heights table and then grab all the heights that are valid for the range
Yes, I grabbed this example from the following question: TSQL – If..Else statement inside Table-Valued Functions – cant go through…. As you can see several people answered with how to use a CASE statement instead. That is all nice and dandy but the information should be in a table instead
Single Source of Truth
The thing that is wrong with that piece of code posted is not the fact that the IF statement doesn’t work but the fact that a table was not used to store the info. If you are using a table then you can populate a drop down in a form, use the table in the function, in other procedures, views etc etc. If you need to make a change, you do it in one place and one place only.
This is a very important thing to understand, if you have the same information from 10 different sources all over the place hen when you need to make a change you are bound to make mistakes. Can you imagine if you need to store tax information, having this hardcoded like that? That is insanity.
It is not difficult to create such a table.
Here is a sample table
CREATE TABLE Heights ( HeightId int primary key not null, HeightDescription varchar(20) not null, StartRange smallint not null, EndRange smallint not null) GO
Now let’s insert some data
INSERT Heights values(1,'Small',0,154) INSERT Heights values(2,'Average',155,175) INSERT Heights values(3,'Tall',176,300)
Now if I want to know if 181 centimeters is considered tall or not, I can run this
SELECT HeightDescription FROM Heights WHERE 181 between StartRange and EndRange
Let’s continue by adding another table, this table will have some people and their height.
CREATE TABLE Players(Player varchar(200),Height smallint) GO
The following people are the shortest and tallest people on record
INSERT Players values('Robert Wadlow',272) INSERT Players values('John Rogan',267) INSERT Players values('June Rey Balawing',56) INSERT Players values('Gul Mohammed',57) INSERT Players values('Pauline Musters',58)
Now when I want to list all the tall people, my query looks like this
SELECT p.* FROM Players p JOIN Heights h on p.Height between h.StartRange and h.EndRange where h.HeightDescription = 'Tall'
When I want to list all the tall people, my query looks like this
SELECT p.* FROM Players p JOIN Heights h on p.Height between h.StartRange and h.EndRange where h.HeightDescription = 'Small'
If tomorrow I decide that tall people have to be taller than 180 centimeters, I only have to change this in 1 place. This is much cleaner and also easily maintainable.
Yes, I know, instead of having a player column I should have at least first and last name columns (preferably in a person table) but in the day and age of internet induced ADD I didn’t feel it would add anything to the point I was trying to make in this post……so forgive me