pkfox Ответов: 2

Более элегантный способ сделать это

Привет всем, я унаследовал функцию ниже и хотел бы найти более элегантный способ сделать это - список на самом деле доходит до 13 операторов if, но я обрезал его для этого вопроса, в таблице, о которой идет речь, есть только одна строка

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


Что я уже пробовал:

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

2 Ответов

Рейтинг:
6

CHill60

Альтернатива решению 1, но вы, возможно, не сможете его реализовать.

Я также был в положении, когда я унаследовал" постоянную " таблицу данных, где столбцы использовались таким образом. К счастью, я был в состоянии сменить стол, так как он использовался только в одном месте.

Если вам тоже так повезло, могу ли я предложить изменить таблицу SYSPAR_NML11 с таблицы с 12 столбцами и 1 строкой на таблицу с 2 столбцами и 12 строками ...

create table SYSPAR_NML11_Replacement
(
	period int,
	period_descr varchar(16)
)
Который вы можете заполнить из оригинала 
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
Тогда весь выбор становится очень простым:
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')
На самом деле вы, вероятно, вообще обойдетесь без этой функции.

Вы, вероятно, найдете другие таблицы параметров, которые делают подобные вещи - опять же, если вы можете повлиять на схему, поощряйте их изменение с многоколоночных мерзостей на что-то вроде:
create table SYS_PARMS
(
	id int identity(1,1),
	parm_descr nvarchar(125),
	parm_value nvarchar(125),
	parm_comment nvarchar(125)
)
и т. д. и т. д.

[РЕДАКТИРОВАТЬ]
OP, к сожалению, подтвердил, что они не могут настроить схему таблицы.
Однако, используя UNPIVOT мы можем создать временную таблицу в нужном нам формате:
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')

Kornfeld Eliyahu Peter

Для меня смена схемы всегда была мечтой, даже она оказалась лучшим решением для всех...: thumbsup:

CHill60

"всегда мечта"... Так что правда, как правило! Пытаясь убедить менеджера, который ничего не знает о реляционных базах данных, что схема просто ужасна, но когда она была разработана их любимым "экспертом" (читай ... парень с которым они играют в гольф)

pkfox

К сожалению я не могу изменить схему но спасибо за ваш ответ

CHill60

Это позор. Однако я разработал альтернативу - см. мое пересмотренное решение после "[EDIT]"

pkfox

Где вы объявляете #temp ? кроме того, в таблице нет столбца периода, только period_desc_0 ... и т. д

CHill60

#temp генерируется запросом. Как временная таблица, созданная select...into его не нужно ни объявлять, ни создавать заранее.
В столбце период #темп генерируется от отмены свертывания.
Попробуйте сделать это вне функции и просто запросите таблицу#temp, чтобы увидеть, что произойдет.
Это можно расширить, если эта таблица данных получает дополнительные столбцы, вам нужно только добавить имя столбца в unpivot

pkfox

Хорошо спасибо за разъяснение

Maciej Los

5ед!

pkfox

Привет я попробовал ваш код и получил эту ошибку

(13 строк) затронуты)
Msg 245, Уровень 16, Состояние 1, Строка 17
Ошибка преобразования в тип varchar значение 'описание 0' в тип данных int.

IF OBJECT_ID ('tempdb..#temp') НЕ ЯВЛЯЕТСЯ НУЛЕВЫМ
DROP TABLE #temp

выберите u. Descrip, u. period
В #temp
из описания
оператор UNPIVOT
(
период для описания в (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)
) у;

объявить @period int = 1
объявить @RetVal varchar(16)
Выберите @RetVal = Descrip из #temp, где period = @period
выберите ISNULL(@RetVal, 'Out of range')

CHill60

Мои извинения, у меня были цифры в поле описания, и код сделал неявное преобразование, которое я полностью пропустил.
Я обновил ответ, чтобы исправить ошибку.

pkfox

вот к чему я пришел, основываясь на вашем предложении

Создайте функцию [dbo].[GetDescription] (@Period int)
Возвращает varchar(16)
АС
НАЧАТЬ
Объявить @RetVal varchar(110);

DECLARE @tempTable table (period int identity(0,1), period_descr varchar(16))
вставить в @tempTable
выберите u. период
из описания с

оператор UNPIVOT
(
период для описания в (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)
) у;

Выберите @код = period_descr от @поддающийся соблазну где период = @период
возвращение @RetVal
КОНЕЦ

Большое спасибо за вашу помощь, я даже не слышал о unpivot ! очень полезная информация, в качестве примечания вы не можете использовать временные таблицы #temp в функции

CHill60

*хлопает по голове* - еще раз извини! Без временных таблиц в функции ловит меня каждый раз. Однако вы нашли переменную workaround - table.
Я слышал о unpivot довольно давно, но это первый раз, когда я использовал его для реальной проблемы...думаю, это показывает! ;-)

Рейтинг:
12

Kornfeld Eliyahu Peter

Я бы сказал, что динамический 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[^]

pkfox

Привет, Питер, к сожалению, вы не можете использовать sp_executesql в функции-спасибо за ваш ответ

Kornfeld Eliyahu Peter

Я бы не сказал, что это намного элегантнее, но вы можете создать такой вид:
SELECT 0, (SELECT TOP 1 PERIOD_DESCR_0 FROM SYSPAR_NML11) UNION
SELECT 1, (SELECT TOP 1 PERIOD_DESCR_1 FROM SYSPAR_NML11) UNION
ВЫБЕРИТЕ 2, (ВЫБРАТЬ ПЕРВЫЕ 1 PERIOD_DESCR_2 ОТ SYSPAR_NML11) СОЮЗ
SELECT 3, (SELECT TOP 1 PERIOD_DESCR_3 FROM SYSPAR_NML11) UNION
ВЫБЕРИТЕ 4, (ВЫБРАТЬ ПЕРВЫЕ 1 PERIOD_DESCR_4 ОТ SYSPAR_NML11) ...

И идите с раствором из CHill60 оттуда...

CHill60

Я обновил его - использовал UNPIVOT, чтобы сделать что-то подобное. Первый раз я использовал UNPIVOT в гневе! :-)

Maciej Los

5ед!

Kornfeld Eliyahu Peter

Спасибо...