More elegant way of doing this

Question

3.00/5 (2 votes)

See more:

Hi all, I've inherited the function below and would like to find a more elegant way of doing it - the list actually goes up to 13 if statements but I clipped it for this question, the table in question only has one row

SQL

Create function [dbo].[Get_period_descr](@period int)
 returns varchar(16)
AS
BEGIN
declare @RetVal varchar(16)

if Not (@period >= 0 and @period <= 13)
	Select @RetVal = 'Out of range'
else
    if @period = 0
        Select  @RetVal = period_descr_0 from SYSPAR_NML11
    else
        if @period = 1
            Select  @RetVal = period_descr_1 from SYSPAR_NML11
        else
            if @period = 2
                Select  @RetVal = period_descr_2 from 	SYSPAR_NML11
return @RetVal
END

What I have tried:

SQL

Create function [dbo].[Get_period_descr](@period int)
 returns varchar(16)
AS
BEGIN
declare @RetVal varchar(16)

if Not (@period >= 0 and @period <= 13)
	Select @RetVal = 'Out of range'
else
    if @period = 0
        Select  @RetVal = period_descr_0 from SYSPAR_NML11
    else
        if @period = 1
            Select  @RetVal = period_descr_1 from SYSPAR_NML11
        else
            if @period = 2
                Select  @RetVal = period_descr_2 from 	SYSPAR_NML11
return @RetVal
END

Posted 26-Apr-17 1:41am

pkfox

Updated 26-Apr-17 2:38am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2017-04-26T01:51:00

Solution 1

I would say a dynamic SQL can help you here...

SQL

DECLARE @PERIOD INT = 1
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @RETVAL VARCHAR(16)

SET @SQL = 'SELECT @RV = PERIOD_DESCR_' + CAST(@PERIOD AS NVARCHAR) + ' FROM SYSPAR_NML11'
EXECUTE sp_executesql @SQL, N'@RV VARCHAR(16) OUT', @RV = @RETVAL OUT

SELECT @RETVAL

sp_executesql (Transact-SQL) | Microsoft Docs[^]

Posted 26-Apr-17 1:51am

Kornfeld Eliyahu Peter

Comments

pkfox 26-Apr-17 9:46am

Hi Peter unfortunately you can't use sp_executesql in a function - thanks for your answer

Kornfeld Eliyahu Peter 26-Apr-17 10:08am

I wouldn't say that is is much more elegant, but you can create a view like this:
SELECT 0, (SELECT TOP 1 PERIOD_DESCR_0 FROM SYSPAR_NML11) UNION
SELECT 1, (SELECT TOP 1 PERIOD_DESCR_1 FROM SYSPAR_NML11) UNION
SELECT 2, (SELECT TOP 1 PERIOD_DESCR_2 FROM SYSPAR_NML11) UNION
SELECT 3, (SELECT TOP 1 PERIOD_DESCR_3 FROM SYSPAR_NML11) UNION
SELECT 4, (SELECT TOP 1 PERIOD_DESCR_4 FROM SYSPAR_NML11) ...

And go with the solution from CHill60 from there...

CHill60 26-Apr-17 10:48am

I've updated it - used UNPIVOT to do something similar. First time I've used UNPIVOT in anger! :-)

Maciej Los 26-Apr-17 15:39pm

5ed!

Kornfeld Eliyahu Peter 28-Apr-17 0:21am

Thank you...

CHill60 · Accepted Answer · 2017-04-26T02:38:00

An alternative to Solution 1 but one you may not be able to implement.

I have also been in a position where I inherited a "standing" data table where columns had been used like this. I was luckily in a position to change the table as it was only used in one place.

If you are also that lucky, can I suggest changing the table SYSPAR_NML11 from a table with 12 columns and 1 row, to a table with 2 columns and 12 rows ...

SQL

create table SYSPAR_NML11_Replacement
(
	period int,
	period_descr varchar(16)
)

Which you can populate from the original

SQL

INSERT INTO SYSPAR_NML11_REPLACEMENT
SELECT 1 as O, period_descr_1 as period_descr FROM SYSPAR_NML11_Original UNION SELECT 2, period_descr_2 FROM SYSPAR_NML11_Original UNION 
SELECT 3, period_descr_3 FROM SYSPAR_NML11_Original UNION SELECT 4, period_descr_4 FROM SYSPAR_NML11_Original UNION 
SELECT 5, period_descr_5 FROM SYSPAR_NML11_Original UNION SELECT 6, period_descr_6 FROM SYSPAR_NML11_Original UNION
SELECT 7, period_descr_7 FROM SYSPAR_NML11_Original UNION SELECT 8, period_descr_8 FROM SYSPAR_NML11_Original UNION 
SELECT 9, period_descr_9 FROM SYSPAR_NML11_Original UNION SELECT 10, period_descr_10 FROM SYSPAR_NML11_Original UNION 
SELECT 11, period_descr_11  FROM SYSPAR_NML11_Original UNION SELECT 12, period_descr_12 FROM SYSPAR_NML11_Original
ORDER BY 1

Then the whole select becomes very simple:

SQL

declare @period int = 10
declare @RetVal varchar(16)

SELECT @RetVal = period_descr from SYSPAR_NML11_REPLACEMENT WHERE period = @period

select ISNULL(@RetVal, 'Out of range')

In fact you would probably dispense with the function altogether.

You will probably find other parameter tables that do similar things - again, if you can influence the schema, encourage these to be changed from multi-column abominations to something like:

create table SYS_PARMS
(
	id int identity(1,1),
	parm_descr nvarchar(125),
	parm_value nvarchar(125),
	parm_comment nvarchar(125)
)

etc etc

[EDIT]
OP has unfortunately confirmed that they cannot adjust the table schema.
However, using UNPIVOT we can create a temporary table in the format we need:

SQL

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
  DROP TABLE #temp

create table #temp (period int identity(0,1), Descrip nvarchar(16))

insert into #temp
select u.period
from SYSPAR_NML11 s
unpivot
(
  period for Descrip in (period_descr_0,period_descr_1,period_descr_2,
period_descr_3,period_descr_4,period_descr_5,
period_descr_6,period_descr_7,period_descr_8,
period_descr_9,period_descr_10,period_descr_11,
period_descr_12)
) u;

declare @RetVal varchar(16)
SELECT @RetVal = Descrip from #temp WHERE period = @period
select ISNULL(@RetVal, 'Out of range')