如何创建返回的临时表？

Question

我正在构建一个存储过程，它将充当一个配置字符串代码读取器，它将接受一个@config varchar(255)变量。

此配置字符串确定我们模型中的26个设置。

接受其中一个配置字符串条目：

declare @casingType varchar(50);
set @casingType=case SubString(@config, 34, 1)
  when '1' then 'U-Flange - All Around w/ Stacking Flanges'
  when '2' then 'U-Flange - All Around'
  when '3' then 'U-Flange - No Top & Bottom'
  when '4' then 'U-Flange - Flat Top & Bottom'
  when '5' then 'Box Bracket - End Plates Only'
  when '6' then 'Box Bracket - All Around'
  when '7' then 'Slip & Drive Bracket'
  when '8' then 'L Flange'
  when '0' then 'No Casing'
  when 'A' then '3 Sided Box - No Top & Bottom'
  when 'B' then '3 Sided Box - Top & Bottom'
  when 'C' then '3 Sided Box - Top or Bottom'
  when 'D' then 'U-Flange w/ Stacking Plates'
  when 'E' then 'U-Flange Temp Top & Bottom'
  when 'F' then 'Flat Bracket'
  when 'G' then 'A Coil Slab Bracket'
  when 'H' then '2 Sided Box'
  when 'I' then '3 Sided Box w/ Temp Top & Bottom'
  when 'O' then 'One Plus One Casing'
  when 'X' then 'Special'
  when 'Y' then 'Auto Braze'
  else 'Error' end;

我要返回的是一个包含每个项的文本字段的一行的表。

是创建要返回的临时表还是创建要返回的其他类型的表？

我希望这是合理的。可能我的措辞不正确，或者使用了不恰当的SQL术语。

解决方案：--所以，我把它做好了，下面是我用来做的代码。

通常，在配置字符串中工作的销售人员或工程师不知道某个字母是什么意思，所以他们必须查找它。超过70%的时间，这会导致更多的字母代码搜索。因此，我们只想返回与特定配置相关的所有细节。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Joe Pool
-- Create date: 21-22 January 2013
-- Description: This returns a DataTable representation of the Model Configuration
-- =============================================
CREATE PROCEDURE sp1_Configurator(@config varchar(255)) as
BEGIN
SET NOCOUNT ON;
declare @len int;
declare @coilType varchar(50), @coilPattern varchar(50), @rowsDeep varchar(50);
declare @finHeight varchar(50), @finLength varchar(50), @finThickMat varchar(50);
declare @finPerInch varchar(50), @finTreatment varchar(50), @finCoating varchar(50);
declare @tubeWallThk varchar(50), @tubeType varchar(50), @qty varchar(50);
declare @tubeCoat varchar(50), @gauge varchar(50), @material varchar(50);
declare @casingType varchar(50), @customerCode varchar(50), @caseCoat varchar(50);
declare @arrangement varchar(50), @connType varchar(50), @connSize varchar(50);
declare @distributor varchar(50), @circuitry varchar(50), @coilApp varchar(50);
declare @agency varchar(50), @outsideCoat varchar(50);
declare @table table (
  CoilType varchar(50) null, CoilPattern varchar(50) null, RowsDeep varchar(50) null, 
  FinHeight varchar(50) null, FinLength varchar(50) null, FinThickMat varchar(50) null, 
  FinPerInch varchar(50) null, FinTreatment varchar(50) null, FinCoating varchar(50) null, 
  TubeWallThk varchar(50) null, TubeType varchar(50) null, Qty varchar(50) null, 
  TubeCoat varchar(50) null, Gauge varchar(50) null, Material varchar(50) null, 
  CasingType varchar(50) null, CustomerCode varchar(50) null, CaseCoat varchar(50) null, 
  Arrangement varchar(50) null, ConnType varchar(50) null, ConnSize varchar(50) null, 
  Distributor varchar(50) null, Circuitry varchar(50) null, CoilApp varchar(50) null, 
  Agency varchar(50) null, OutsideCoat varchar(50) null
);
set @len=Len(@config)
if (@len=53) begin
  set @coilType=case SubString(@config, 1, 1)
    when 'C' then 'Slab' 
    when 'B' then '1 + 1'
    when 'A' then 'A Coil (OBS)'
    when 'X' then 'Special'
    else 'Error' end;
  set @coilPattern=case SubString(@config, 2, 1)
    when '7' then '7mm Tube (.827 x .472 Staggered)'
    when '6' then '5/16" Tube (1 x 5/8 Staggered)(OBS)'
    when '3' then '3/8" Tube (1 x .866 Staggered)'
    when 'P' then '1/2" Tube (1 1/4 x 1.08 Staggered)'
    when '5' then '5/8" Tube (1 1/2 x 1.299 Staggered)'
    else 'Error' end;
  set @rowsDeep=SubString(@config, 3, 2);
  set @finHeight=SubString(@config, 6, 5);
  set @finLength=SubString(@config, 12, 6);
  set @finThickMat=case SubString(@config, 19, 1)
    when 'A' then '.0045 AL (OBS)'
    when 'J' then '.0055 AL (OBS)'
    when 'B' then '.0060 AL'
    when 'C' then '.0075 AL'
    when 'D' then '.0100 AL'
    when 'E' then '.0045 CU (OBS)'
    when 'K' then '.0050 CU (OBS)'
    when 'F' then '.0060 CU'
    when 'G' then '.0075 CU (OBS)'
    when 'H' then '.0100 CU (OBS)'
    when 'P' then '.0065 Pre-Coated'
    when 'X' then 'Special'
    else 'Error' end;
  set @finPerInch=SubString(@config, 20, 2);
  set @finTreatment=case SubString(@config, 22, 2)
    when 'FS' then 'Flat / Straight (OBS)'
    when 'FR' then 'Flat / Rippled (OBS)'
    when 'CS' then 'Corrugated / Straight (OBS)'
    when 'CR' then 'Corrugated / Rippled'
    when 'SS' then 'Sine / Straight (OBS)'
    when 'SR' then 'Sine / Rippled'
    when 'LS' then 'Louvered / Straight (OBS)'
    when 'LR' then 'Louvered / Rippled (OBS)'
    when 'RL' then 'Embossed Arch / Rippled*'
    when 'XX' then 'Special'
    else 'Error' end;
  set @finCoating=case SubString(@config, 24, 1)
    when 'N' then 'See Coil Coating'
    when 'A' then 'Alodine (OBS)'
    when 'K' then 'Technicoat'
    when 'P' then 'Paint Bond'
    when 'X' then 'Special'
    else 'Error' end;
  set @tubeWallThk=case SubString(@config, 26, 1)
    when 'A' then '.012 Smooth (5/16)(OBS)'
    when 'B' then '.014 Smooth (3/8)(OBS)'
    when 'C' then '.017 Smooth (1/2)'
    when 'D' then '.018 Smooth (5/8)'
    when 'E' then '.025 Smooth (1/2, 5/8)'
    when 'F' then '.035 Smooth (1/2, 5/8)'
    when 'G' then '.049 Smooth (5/8)'
    when 'H' then '.012 Rifled (3/8)'
    when 'K' then '.012 Rifled (7mm)'
    when 'L' then '.016 Rifled (7mm) (OBS)'
    when 'P' then '.06 Rifled (1/2)'
    when 'X' then 'Special (.016 Rifled 3/8)(OBS)'
    else 'Error' end;
  set @tubeType=case SubString(@config, 27, 1)
    when '1' then 'ST Flexpand'
    when '2' then 'ST ALL DL Flexpand'
    when '3' then 'ST w/DLST Flexpand'
    when '5' then 'HP Flexpand'
    when '6' then 'HP w/ST Flexpand'
    when 'A' then 'ST w/DL*'
    when 'B' then 'HP w/DL*'
    when 'C' then 'Hairpin w/Straight*'
    when 'D' then 'Hairpin 1/Hairpin .8* (OB)'
    when 'E' then 'HP / DL / ST*'
    when 'F' then 'HP / DL / ST / ST DL*'
    when 'G' then 'HP / DL ST*'
    when 'H' then 'Hairpint (HP)'
    when 'I' then 'HP / ST / DL HP*'
    when 'J' then '.8 HP* (OBS)'
    when 'K' then 'One Short HP / One Long HP*'
    when 'L' then 'HP w/Special DL*'
    when 'M' then 'HP w/Special DL / ST*'
    when 'N' then 'HP 1/HP .8/ST* (OBS)'
    when 'O' then 'HP 1/HP .8/DL 1 HP/DL .8 HP* (OBS)'
    when 'P' then 'All DL ST'
    when 'Q' then 'ST w/Special DL ST*'
    when 'R' then 'HP .8 / ST* (OBS)'
    when 'S' then 'Straight (ST)'
    when 'T' then 'Hydro Ball (HB)'
    when 'U' then 'HB w/ DL*'
    when 'V' then 'ST w/Spin Down*'
    when 'W' then 'HP 7mm .827/.627 Angle*'
    when 'Y' then 'HP 7mm All .627*'
    when 'X' then 'Special*'
    else 'Error' end;
  set @qty=SubString(@config, 28, 2);
  set @tubeCoat=case SubString(@config, 30, 1)
    when 'N' then 'No Coating'
    when 'T' then 'Tinned Plating (OBS)'
    when 'X' then 'Special'
    else 'Error' end;
  set @gauge=case SubString(@config, 32, 1)
    when '2' then '20 Gauge'
    when '8' then '18 Gauge'
    when '6' then '16 Gauge'
    when '4' then '14 Gauge'
    when '5' then '0.050" THK'
    when '3' then '0.063" THK'
    when '9' then '0.090" THK'
    when 'X' then 'Special'
    else 'Error' end;
  set @material=case SubString(@config, 33, 1)
    when 'G' then 'Galvanized'
    when 'S' then 'Stainless'
    when 'C' then 'Copper'
    when 'A' then 'Aluminum'
    when 'P' then 'Paint Bond'
    when 'X' then 'Special'
    else 'Error' end;
  set @casingType=case SubString(@config, 34, 1)
    when '1' then 'U-Flange - All Around w/ Stacking Flanges'
    when '2' then 'U-Flange - All Around'
    when '3' then 'U-Flange - No Top & Bottom'
    when '4' then 'U-Flange - Flat Top & Bottom'
    when '5' then 'Box Bracket - End Plates Only'
    when '6' then 'Box Bracket - All Around'
    when '7' then 'Slip & Drive Bracket'
    when '8' then 'L Flange'
    when '0' then 'No Casing'
    when 'A' then '3 Sided Box - No Top & Bottom'
    when 'B' then '3 Sided Box - Top & Bottom'
    when 'C' then '3 Sided Box - Top or Bottom'
    when 'D' then 'U-Flange w/ Stacking Plates'
    when 'E' then 'U-Flange Temp Top & Bottom'
    when 'F' then 'Flat Bracket'
    when 'G' then 'A Coil Slab Bracket'
    when 'H' then '2 Sided Box'
    when 'I' then '3 Sided Box w/ Temp Top & Bottom'
    when 'O' then 'One Plus One Casing'
    when 'X' then 'Special'
    when 'Y' then 'Auto Braze'
    else 'Error' end;
  set @customerCode=case SubString(@config, 35, 5)
    when '00' then 'Standard'
    when '14' then 'AAON Damper'
    when '15' then 'AAON Cond.'
    when '16' then 'AAON Evap.'
    when 'XX' then 'Special'
    else 'Error' end;
  set @caseCoat=case SubString(@config, 37, 1)
    when 'N' then 'See Coil Coating'
    when 'A' then 'Alodine (OBS)'
    when 'C' then 'Ceramic'
    when 'X' then 'Special'
    else 'Error' end;
  set @arrangement=SubString(@config, 39, 2);
  set @connType=case SubString(@config, 41, 1)
    when '0' then 'No Connection'
    when 'M' then 'MPT'
    when 'F' then 'FPT'
    when 'S' then 'Sweat'
    when 'W' then 'Water Bead (OBS)'
    when 'B' then 'Barbed FTG (OBS)'
    when 'N' then 'Male Flare'
    when 'G' then 'Female Flare'
    when 'O' then 'Male O-Ring (OBS)'
    when 'P' then 'Female O-Ring (OBS)'
    when 'X' then 'Special'
    else 'Error' end;
  set @connSize=case SubString(@config, 42, 1)
    when '0' then 'No Connection'
    when '1' then '3/8 OD'
    when '2' then '1/2 OD'
    when '3' then '5/8 OD'
    when '4' then '7/8 OD'
    when '5' then '1-1/8 OD'
    when '6' then '1-3/8 OD'
    when '7' then '1-5/8 OD'
    when '8' then '2-1/8 OD'
    when '9' then '2-5/8 OD'
    when 'A' then '3-1/8 OD'
    when 'B' then '5/16 OD'
    when 'C' then '3/4 OD'
    when 'D' then '4-1/8 OD'
    when 'E' then '3/16 OD'
    when 'X' then 'Special'
    else 'Error' end;
  set @distributor=case SubString(@config, 44, 4)
    when 'N000' then 'None Required'
    when 'X001' then 'Special'
    else 'Factory Assigned' end;
  set @circuitry=case SubString(@config, 49, 2)
    when 'SS' then 'Single Circuit'
    when 'FF' then 'Full'
    when 'HH' then 'Half'
    when 'QQ' then 'Quarter'
    when 'DD' then 'Double'
    when 'DH' then '1-1/2'
    when 'II' then 'Intertwined <2'
    when 'RS' then 'Row Split <2'
    when 'FS' then 'Face Split <2'
    when '00' then 'No Circuitry'
    when '01' then 'One Circuit'
    when '02' then 'Two Circuits'
    when '03' then 'Three Circuits'
    when '04' then 'Four Circuits'
    when '0S' then 'No Circuitry + SubCooler'
    when '1S' then 'One Circuit + SubCooler'
    when '2S' then 'Two Circuits + SubCooler'
    when '3S' then 'Three Circuits + SubCooler'
    when 'XX' then 'Special'
    else 'Error' end;
  set @coilApp=case SubString(@config, 51, 1)
    when 'D' then 'Drainable Water'
    when 'W' then 'Water'
    when 'G' then 'Cond / SubCooler'
    when 'C' then 'Condenser'
    when 'E' then 'Evaporator'
    when 'S' then 'Steam'
    when 'N' then 'Steam Distributor'
    when 'B' then 'Booster'
    when 'H' then 'Heat Reclaim'
    when 'P' then 'Heat Pipe (OBS)'
    when 'L' then 'Glycol'
    when 'O' then 'Oil (OBS)'
    when 'X' then 'Special'
    else 'Error' end;
  set @agency=case SubString(@config, 52, 1)
    when '0' then 'None'
    when 'A' then 'ARI'
    when 'B' then 'ARI + UL / CSA'
    when 'C' then 'UL / CSA'
    when 'E' then 'ETL / DOE'
    else 'Error' end;
  set @outsideCoat=case SubString(@config, 53, 1)
    when 'N' then 'No Coating'
    when 'A' then 'Ceramic'
    when 'C' then 'Chromocoat'
    when 'E' then 'Epoxy'
    when 'G' then 'Americoat Grey (OBS)'
    when 'H' then 'Heresite'
    when 'K' then 'Phenolic (Technicoat)'
    when 'L' then 'ElectroFin'
    when 'P' then 'Phenolic (OBS)'
    when 'X' then 'Special'
    else 'Error' end;
  insert into @table
    (CoilType, CoilPattern, RowsDeep, FinHeight, FinLength, FinThickMat, FinPerInch, FinTreatment, FinCoating,
     TubeWallThk, TubeType, Qty, TubeCoat, Gauge, Material, CasingType, CustomerCode, CaseCoat, Arrangement,
     ConnType, ConnSize, Distributor, Circuitry, CoilApp, Agency, OutsideCoat)
     values
    (@coilType, @coilPattern, @rowsDeep, @finHeight, @finLength, @finThickMat, @finPerInch, @finTreatment, @finCoating,
    @tubeWallThk, @tubeType, @qty, @tubeCoat, @gauge, @material, @casingType, @customerCode, @caseCoat, @arrangement,
    @connType, @connSize, @distributor, @circuitry, @coilApp, @agency, @outsideCoat);
end
select * from @table;
END
GO

下面是实际的配置字符串：

CP06-51.25-051.50-B12SRN-CT00N-8G2XXN-A2S8-N000-HHWAN

Answer 1

您是否考虑过创建一个用户定义的函数：

create function CastingTypeFunction(@config varchar(255))
returns varchar(50)
as
begin
  declare @casingType varchar(50)
  set @casingType=case SubString(@config, 34, 1)
    when '1' then 'U-Flange - All Around w/ Stacking Flanges'
    when '2' then 'U-Flange - All Around'
    when '3' then 'U-Flange - No Top & Bottom'
    when '4' then 'U-Flange - Flat Top & Bottom'
    when '5' then 'Box Bracket - End Plates Only'
    when '6' then 'Box Bracket - All Around'
    when '7' then 'Slip & Drive Bracket'
    when '8' then 'L Flange'
    when '0' then 'No Casing'
    when 'A' then '3 Sided Box - No Top & Bottom'
    when 'B' then '3 Sided Box - Top & Bottom'
    when 'C' then '3 Sided Box - Top or Bottom'
    when 'D' then 'U-Flange w/ Stacking Plates'
    when 'E' then 'U-Flange Temp Top & Bottom'
    when 'F' then 'Flat Bracket'
    when 'G' then 'A Coil Slab Bracket'
    when 'H' then '2 Sided Box'
    when 'I' then '3 Sided Box w/ Temp Top & Bottom'
    when 'O' then 'One Plus One Casing'
    when 'X' then 'Special'
    when 'Y' then 'Auto Braze'
    else 'Error' end

    return @casingType
end;

然后将您的@config值传递给类似于以下内容的函数：

 select dbo.CastingTypeFunction(@config)

然后，您的价值将被返回。

见与Demo。

我可以看到的另一个选项是创建一个包含每个值的表，然后在该表上加入返回casingType。

这些表格将与此类似：

CREATE TABLE CasingType
    ([CasingTypeId] varchar(1), [CasingValue] varchar(41))
;

然后你会加入它，类似于这样：

select 
  case 
    when c.casingtypeid is null 
    then 'Error'
    else c.casingvalue end
from
(
  select '12323212334234231211231212121qwe1212312334234234' config  -- replace with your config values
) src
left join CasingType c
  on SubString(src.config, 34, 1) = c.casingtypeid

请参阅与Demo