时间戳

首页 > 解决方案 > 检查值并返回结果的函数

sql-server - 检查值并返回结果的函数

问题描述

我需要在 SQL 中创建函数来检查变量或参数中的数据，如下所示

@Category  as varchar(50)='ABC,DEF'
@Value as varchar(50)='1,2'

并将@Category 值与表中的类别进行比较，然后从参数返回匹配的值

JOB TABLE ---

JOB   CATEGORY
123   ABC
234   DEF
234   SSS

Select JobNo,FUNCTION(@Category,@Value,CATEGORY) from JOB 


FINAL RESULTS 

JOB   VALUE
123   1
234   2
234   0

如果类别匹配，则从参数返回值，否则返回 0。

标签： sql-servertsqlstored-functions

解决方案

如果您不能使用注释中提到的静态查找表（例如，映射可能需要基于应用程序提供的数据是动态的），那么这看起来像是一个表值参数的工作。

现在你有两个参数，但在我看来，参数中的值是相关的。也就是说，现在你有@category = 'ABC,DEF'and @value = '1,2'，但我认为你打算用逗号分隔的“类别”集中的每个“元素”与相同的逗号分隔的“值”集中的“元素”相关联位置。

现在设计很脆弱，因为如果我使用这样的参数会发生什么：@category = 'ABC,DEF,GHI,JKL', @value = '1'？

因此，您可以使您的代码更持久，并使用注释中向您推荐的那种“基于联接”的查找表解决方案，方法是使用一个接受表值参数参数的函数。为此，您首先必须在数据库中创建表值参数作为类型。然后我们接受该类型的参数，并加入它。在下面的解决方案中，我已经“猜测”了数据类型，category并且value根据您问题中的示例数据，这似乎是合理的。

此外，我保留了您解决方案的“结构”——即，函数的编写方式可以单独“应用于”工作中的每一行。我们不必这样做。相反，我们可以只在函数内部执行整个查询（即，包括对job表的连接），但也许您想对也有cateogry列的其他表使用该函数？我不会尝试在这里猜测整体设计。但出于性能原因，我会将函数切换为内联表值函数（返回一行一列）而不是标量函数。

-- schema and data to match your question
create table dbo.job (job int, category char(3));
insert dbo.job(job, category) values (123, 'ABC'), (234, 'DEF'), (234, 'SSS');
go

-- solution
create type dbo.CategoryValues as table 
(
   category char(3) unique, 
   [value] int
);
go

create or alter function dbo.MapCategory(@category char(3), @map dbo.CategoryValues readonly) 
returns table as return
(
   select [value] from @map where category = @category
);
go

-- to call the function we need to pass a parameter of type 
-- dbo.CategoryValues with the mappings we desire
declare @map dbo.CategoryValues;
insert @map values ('ABC', 1), ('DEF', 2)

-- outer apply the function to each row in the job table.
select      j.job, [value] = isnull(v.[value], 0)
from        dbo.job                             j
outer apply dbo.MapCategory(j.category, @map)   v

推荐阅读