Like的SQL String数组参数

I have an MS SQL stored procedure to which I need to pass in a string array to for use with the LIKE operator. How am I best to achieve this, as there are no built in datatypes that I've come across to achieve this? I've seen implementations whereby a varchar is passed with a delimiter and logic in the SQL script splits this and dynamically builds up the statement.

我有一个MS SQL存储过程，我需要在其中传递一个字符串数组，以便与LIKE运算符一起使用。我是如何最好地实现这一点的，因为我没有实现这种内置数据类型？我已经看到了一个实现，其中varchar与分隔符一起传递，SQL脚本中的逻辑将其拆分并动态构建语句。

None of these implementations really seem clean to me, how is this best achieved?

这些实现对我来说都不是真的很干净，这是如何最好地实现的？

Thanks

谢谢

DECLARE @ContractId int
DECLARE @QuantityPerVehicle decimal(18, 4)
DECLARE @OrderQuantity decimal(18, 4)
DECLARE @OrderDelivered decimal(18, 4)
DECLARE @Description varchar(200)

DECLARE @PendingUsage decimal(18, 4) = 0

DECLARE db_cursor CURSOR FOR select ContractId, QuantityPerVehicle, OrderQuantity, OrderDelivered, Description FROM prod.BillOfMaterialLineDetails where ([description] like '%line item 1%' OR [description] like '%line item 2%' OR [description] like '%line item 3%') and ContractId is not null
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ContractId, @QuantityPerVehicle, @OrderQuantity, @OrderDelivered, @Description

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @JobsRemaining int
        SELECT @JobsRemaining = COUNT(JobId) FROM prod.JobDetails where JobStarted is null and ContractId = @ContractId
        SET @PendingUsage = @PendingUsage + (@JobsRemaining * @QuantityPerVehicle)

        FETCH NEXT FROM db_cursor INTO @ContractId, @QuantityPerVehicle, @OrderQuantity, @OrderDelivered, @Description
    END

CLOSE db_cursor
DEALLOCATE db_cursor


select @PendingUsage as PendingUsage

1 个解决方案

#1

This look's clean for me

这看起来很干净

SELECT contractid, 
       quantitypervehicle, 
       orderquantity, 
       orderdelivered, 
       description 
FROM   prod.billofmateriallinedetails B 
       JOIN (VALUES ('%line item 1%'), 
                    ('%line item 2%'), 
                    ('%line item 3%')) tc(line_item) 
         ON B.description LIKE tc.line_item 
WHERE  contractid IS NOT NULL