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 ...
create table SYSPAR_NML11_Replacement
(
period int,
period_descr varchar(16)
)
Which you can populate from the original
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:
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:
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')