能够对两个VarChar参数进行LIKE操作

Question

我有一个表，看起来像:

orderID  Code  SubCode

基于代码和子代码，我需要能够拉出所有的orderid。

Codes和SubCodes是varchar值，我需要能够对它做一个"喜欢"，因为用户可以，例如，对Code有3%的访问权，对SubCode有45%的访问权对于上面的例子，我将得到所有以3开头的Code和以45开头的SubCode的订单。

我已经得到了它的工作只是Code像这样:

ALTER PROCEDURE [sp_GetOrders]
    -- Add the parameters for the stored procedure here
@codeList varchar(max), 
@subCodeList varchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @tempTable Table
(
SearchCode varchar(max)
)
insert  into @tempTable
select  * from dbo.udf_split(@codeList ) as split --this splits comma delimited list 'm passing in 
    -- Insert statements for procedure here
SELECT  orderID  from ordert t1
where
exists(
select 1 from @tempTable tt
where t1.userCode  like tt.SearchOrg
)
END

上面的工作，如果我在传递300%的codeList，它正确地返回所有的订单id，有任何以300开头的代码，但我现在如何还包括SubCode在混合?

我想这将是更好的，如果我能够传递代码和suborg作为一个参数，不要失去彼此的关系，也许是像"5%-6%"的破折号，其中5%将是代码和6%的suborg..但我甚至不知道从哪里开始。

从后端，它并不重要的格式传递参数，我只需要弄清楚如何让SP工作

Answer 1

在SQLServer2008+中，可以将表值参数传递给sp和udf。表值参数允许您以表格格式将数据行传递给sp和udf。要创建表值参数，必须首先创建一个表类型来定义表结构。

CREATE TYPE dbo.CodeSubCodeValueType
AS TABLE (Code nvarchar(100) NOT NULL, SubCode nvarchar(100) NOT NULL)
GO
ALTER PROCEDURE [sp_GetOrders]
 (@CodeSubCodeValueType dbo.CodeSubCodeValueType READONLY)
AS
BEGIN    
  SET NOCOUNT ON;
  SELECT orderID
  FROM ordert t1
  WHERE EXISTS(
               SELECT 1 
               FROM @CodeSubCodeValueType tt               
               WHERE t1.userCode LIKE tt.Code
                 AND t1.userSubCode LIKE tt.SubCode
               )
END
GO

调用带有表值形参的过程

DECLARE @CodeSubCodeValueType dbo.CodeSubCodeValueType
INSERT @CodeSubCodeValueType
VALUES('5%', '6%'), 
      ('7%', '8%')
EXEC [sp_GetOrders] @CodeSubCodeValueType

如果参数对的值之一为空，则在表类型中使用NULL值是必要的:

CREATE TYPE dbo.CodeSubCodeValueType
AS TABLE (Code nvarchar(100), SubCode nvarchar(100))
GO
ALTER PROCEDURE [sp_GetOrders]
 (@CodeSubCodeValueType dbo.CodeSubCodeValueType READONLY)
AS
BEGIN    
  SET NOCOUNT ON;
  SELECT orderID
  FROM ordert t1
  WHERE EXISTS(
               SELECT 1 
               FROM @CodeSubCodeValueType tt               
               WHERE t1.Code LIKE ISNULL(tt.Code, t1.Code)
                 AND t1.SubCode LIKE ISNULL(tt.SubCode, t1.SubCode)
               )
END
GO

在<<p>简单演示strong> SQLFiddle

Answer 2

您可以考虑将输入参数类型更改为XML

XML格式

DECLARE @inputXML AS XML = 
 '<ParameterList>
    <Parameter>
        <Code>300%</Code>
        <SubCode>5%</SubCode>
    </Parameter>
    <Parameter>
        <Code>10%</Code>
        <SubCode>6%</SubCode>
    </Parameter>
</ParameterList>'

您的存储过程类似于

ALTER PROCEDURE [sp_GetOrders]
    @inputXML XML 
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tempTable TABLE
(
SearchCode VARCHAR(100),
SearchSubCode VARCHAR(100)
)

INSERT  INTO @tempTable
SELECT
   Pars.value('(Code)[1]', 'Varchar(100)') AS Code,
   Pars.value('(SubCode)[1]', 'Varchar(100)') AS SubCode
FROM
   @inputXML.nodes('/ParameterList/Parameter') AS List(Pars)

SELECT  orderID  
FROM ordert t1
WHERE
EXISTS(
    SELECT 1 FROM @tempTable tt
    WHERE t1.userCode  LIKE tt.SearchCode
    AND t1.userSubCode LIKE tt.SearchSubCode
)