September 5, 2013 at 12:54 pm
Hi there. It has been a while since being on here but I have a question. I am trying to create a table that has to be backward compatible with previous versions and it is being a pain. I need to convert and age field into an age group field. Or make a new field for it. either way works.
I am trying to use a UDF because I need this to work on 6 different tables and I dont want to have to type it in all the time, also because i dont like "messy" code.
here is what i have so far.
USE [Test]
GO
/****** Object: UserDefinedFunction [dbo].[AGE_GROUP] Script Date: 09/05/2013 11:40:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[AGE_GROUP](@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN CASE @STRING
WHEN 0 THEN ' 0'
WHEN 1 THEN ' 1'
WHEN 2 THEN ' 2'
WHEN 3 THEN ' 3'
WHEN 4 THEN ' 4'
WHEN 5 THEN ' 5'
WHEN 6 THEN ' 6'
WHEN 7 THEN ' 7'
WHEN 8 THEN ' 8'
WHEN 9 THEN ' 9'
WHEN 10 THEN ' 10'
WHEN 11 THEN ' 11'
WHEN 12 THEN ' 12'
WHEN 13 THEN ' 13'
WHEN 14 THEN ' 14'
WHEN 15 THEN ' 15'
WHEN 16 THEN ' 16'
WHEN 17 THEN ' 17'
WHEN 18 THEN ' 18'
WHEN 19 THEN ' 19'
WHEN BETWEEN 20 AND 24 THEN '20-24'
END
END
There would be age groups like the "when age(between XX and xx) then 'xx-xx'
Does anyone have any ideas on how to do this?
Also it works for the 0-19 just fine.
September 5, 2013 at 2:57 pm
I have 2 ideas, one would be to change your scalar function into an inline table function that will perform much better.
CREATE FUNCTION [dbo].[AGE_GROUP](@string VARCHAR(20))
RETURNS TABLE
BEGIN
RETURN SELECT CASE
WHEN @string = 0 THEN ' 0'
WHEN @string = 1 THEN ' 1'
WHEN @string = 2 THEN ' 2'
WHEN @string = 3 THEN ' 3'
WHEN @string = 4 THEN ' 4'
WHEN @string = 5 THEN ' 5'
WHEN @string = 6 THEN ' 6'
WHEN @string = 7 THEN ' 7'
WHEN @string = 8 THEN ' 8'
WHEN @string = 9 THEN ' 9'
WHEN @string = 10 THEN ' 10'
WHEN @string = 11 THEN ' 11'
WHEN @string = 12 THEN ' 12'
WHEN @string = 13 THEN ' 13'
WHEN @string = 14 THEN ' 14'
WHEN @string = 15 THEN ' 15'
WHEN @string = 16 THEN ' 16'
WHEN @string = 17 THEN ' 17'
WHEN @string = 18 THEN ' 18'
WHEN @string = 19 THEN ' 19'
WHEN @string BETWEEN 20 AND 24 THEN '20-24'
END
Another idea is to create a table with your age groups and join it to your queries.
September 9, 2013 at 2:05 am
You are passing in a VARCHAR(MAX) and treating it like a numeric type in your function. This results in an implicit data type conversion and also leaves you open to someone passing an invalid value to your function, namely a string that cannot be converted to a number.
Try this:
[font="Courier New"]
USE tempdb
GO
CREATE FUNCTION [dbo].[AGE_GROUP] (@age TINYINT)
RETURNS TABLE
AS
RETURN
SELECT CAST(CASE @age
WHEN 0 THEN ' 0'
WHEN 1 THEN ' 1'
WHEN 2 THEN ' 2'
WHEN 3 THEN ' 3'
WHEN 4 THEN ' 4'
WHEN 5 THEN ' 5'
WHEN 6 THEN ' 6'
WHEN 7 THEN ' 7'
WHEN 8 THEN ' 8'
WHEN 9 THEN ' 9'
WHEN 10 THEN ' 10'
WHEN 11 THEN ' 11'
WHEN 12 THEN ' 12'
WHEN 13 THEN ' 13'
WHEN 14 THEN ' 14'
WHEN 15 THEN ' 15'
WHEN 16 THEN ' 16'
WHEN 17 THEN ' 17'
WHEN 18 THEN ' 18'
WHEN 19 THEN ' 19'
WHEN 20 THEN '20-24'
WHEN 21 THEN '20-24'
WHEN 22 THEN '20-24'
WHEN 23 THEN '20-24'
END AS VARCHAR(MAX)) AS AGE_GROUP
GO
-- use it like you would a scalar-function to resolve one value
SELECT age_group
FROM [dbo].[AGE_GROUP](4)
GO
-- how to use this against a resultset:
-- build a temp table with some ages in it
SELECT CAST(1 AS TINYINT) AS age
INTO #tmp
UNION ALL
SELECT CAST(10 AS TINYINT)
UNION ALL
SELECT CAST(20 AS TINYINT);
GO
-- usage option 1 using APPLY (get to know this syntax and how APPLY works)
SELECT t.age,
grp.AGE_GROUP
FROM #tmp t
CROSS APPLY dbo.AGE_GROUP(age) grp
-- usage option 2 - functionally equivalent to usage 1 but might seem more
-- familiar because the function is used in the select-column list much
-- the same way you would write a query that uses a scalar-function
SELECT t.age,
(
SELECT AGE_GROUP
FROM dbo.AGE_GROUP(t.age)
) AS AGE_GROUP
FROM #tmp t;
GO
[/font]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy