更优雅的做法 [英] More elegant way of doing this

问题描述

大家好，我已经继承了下面这个功能，并想找到一个更优雅的方式 - 列表实际上达到了13个if语句但是我为这个问题剪了它，所讨论的表只有一个行

 创建 函数 [dbo]。[Get_period_descr]（ @ period   int ）
 < span class =code-keyword>返回  varchar （ 16 ）
  AS  
  BEGIN  
 声明  @ RetVal   varchar （ 16  ）
 
 如果 不（ @period > =  0  和  @ period < =  13 ）
 选择  @ RetVal  = ' 超出范围' 
  else  
 < span class =code-keyword> if   @ period  =  0  
 选择  @ RetVal  = period_descr_0 来自 SYSPAR_NML11 
  else  
  if   @ period  =  1  
 选择  @ RetVal  = period_descr_1 来自 SYSPAR_NML 11 
  else  
 如果  @期间 =  2  
 选择  @RetVal  = period_descr_2 来自 SYSPAR_NML11 
  return   @ RetVal  
  END  

我的尝试：

 创建  function  [dbo]。[Get_period_descr]（ @ period   int ）
 返回  varchar （ 16 ）
  AS  
  BEGIN  
 声明  @ RetVal   varchar （ 16 ）
 
  if  不（ @ period > =  0  和  @ period < =  13 ）
 选择  @ RetVal  = ' 超出范围 
 其他 
 如果 < span class =code-sdkkeyword> @ period  =  0  
 选择  @ RetVal  = period_descr_0 来自 SYSPAR_NML11 
  else  
 如果  @ period  = < span class =code-digit> 1  
 选择  @ RetVal  = period_descr_1 来自 SYSPAR_NML11 
 其他 
  if   @ period  =  2  
 选择  @ RetVal  = period_descr_2 来自 SYSPAR_NML11 
  return   @ RetVal  
  END  

解决方案

我想说动态SQL可以帮到你......

  DECLA RE   @ 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' 
 < span class =code-keyword> EXECUTE  sp_executesql  @ SQL ，N   @ RV VARCHAR（16）OUT'， @ RV  =  @ RETVAL  OUT 
 
  SELECT   @RETVAL  

sp_executesql（Transact-SQL）| Microsoft Docs [ ^ ]

解决方案1的替代方案，但您可能无法实现。



我还处在一个我继承了常设数据表的位置，其中列已经像这样使用了。我很幸运能够改变桌子，因为它只在一个地方使用。



如果你也很幸运，我可以建议改变表SYSPAR_NML11一个包含12列和1行的表，到一个包含2列和12行的表...

  create  表 SYSPAR_NML11_Replacement 
（
 period  int ，
 period_descr < span class =code-keyword> varchar （ 16 ）
）

您可以从原始填充

  INSERT   INTO  SYSPAR_NML11_REPLACEMENT 
  SELECT   1   as  O，period_descr_1  as  period_descr  FROM  SYSPAR_NML11_Original  UN ION   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 
 订购  BY   1  

然后整个选择变得非常简单：< pre lang =SQL> 声明 @ period int = 10
声明 @ RetVal varchar （ 16 ）

SELECT @ RetVal = period_descr 来自 SYSPAR_NML11_REPLACEMENT WHERE 期间= @ period

选择 ISNULL （ @ RetVal ，' 超出范围'）

事实上你可能会分发完全具有该功能。



你可能会发现其他参数表做类似的事情 - 再次，如果你可以影响架构，鼓励这些参数表从多个 - 列可憎之类如下：

 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'）我S   NOT   NULL  
  DROP  表 #temp 
 
 创建 表 #temp（句号 int   identity （ 0 ， 1 ），Descrip  nvarchar （ 16 ））
 
 插入 进入＃ temp 
 选择 u.period 
 来自 SYSPAR_NML11 s 
 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）
）u; 
 
 声明  @ RetVal   varchar （ 16 ）
  SELECT   @RetVal  = Descrip 来自 #temp  WHERE  period =  @period  
 选择 ISNULL（ @ RetVal ，' 超出范围'）

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

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:

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

解决方案

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

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[^]

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')

这篇关于更优雅的做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！