如何声明IF语句块内的多个变量？

Question

因此，如果我删除了所有的SET @Chars行，那么下面的内容非常有用，不知怎么的，@Chars没有被捡起来。通过直接设置变量和测试1×1进行测试，所以代码很好，变量设置部分有问题。任何建议都非常感谢，谢谢。

这是我的密码：

ALTER PROCEDURE spItemsCompleted (@WorkC AS NVARCHAR(10))
AS
DECLARE @WorkC2 NVARCHAR(10)
DECLARE @Chars INT

BEGIN
IF @WorkC = 'Cut'
  SET @WorkC2 = '%Cut%'
  SET @Chars = 4
END
BEGIN
IF @WorkC = 'Frame'
  SET @WorkC2 = '%Frame%'
  SET @Chars = 6
END
BEGIN
IF @WorkC = 'Asmb'
  SET @WorkC2 = '%Asmb%'
  SET @Chars = 5
END
BEGIN
IF @WorkC = 'Grind'
  SET @WorkC2 = '%Grind%'
  SET @Chars = 6
END
BEGIN
IF @WorkC = 'Paint'
  SET @WorkC2 = '%Paint%'
  SET @Chars = 6
END
BEGIN
IF @WorkC = 'Lock'
  SET @WorkC2 = '%Lock%'
  SET @Chars = 5
END
BEGIN
IF @WorkC = 'Glaze'
  SET @WorkC2 = '%Glaze%'
  SET @Chars = 6
END
BEGIN
IF @WorkC = 'Pack'
  SET @WorkC2 = '%Pack%'
  SET @Chars = 5
END;

SELECT 
    od.OrderNo,
    REVERSE(PARSENAME(REVERSE(od.PartNo),1)) + '.' + 
REVERSE(PARSENAME(REVERSE(od.PartNo),2)) AS [Item],
    CAST(CONVERT(VARCHAR(MAX), CAST(SUBSTRING(o.User_Memo1, 
CHARINDEX(@WorkC,o.User_Memo1)+@Chars, 8) AS DATE), 1) AS DATETIME) AS [Due 
Date]
FROM OrderDet od JOIN Orders o ON od.OrderNo = o.OrderNo
WHERE od.OrderNo NOT IN ('10415', '44444', '77777')
    AND od.OrderNo NOT LIKE '%E'
    AND od.PartNo NOT LIKE '%.999'
    AND o.User_Memo1 LIKE @WorkC2
    AND REVERSE(PARSENAME(REVERSE(od.PartNo),1)) + '.' + 
REVERSE(PARSENAME(REVERSE(od.PartNo),2)) IS NOT NULL
    AND CAST(CONVERT(VARCHAR(MAX), CAST(SUBSTRING(o.User_Memo1, 
CHARINDEX(@WorkC,o.User_Memo1)+@Chars, 8) AS DATE), 1) AS DATETIME) > 
'20180101'
GROUP BY od.OrderNo, REVERSE(PARSENAME(REVERSE(od.PartNo),1)) + '.' + 
REVERSE(PARSENAME(REVERSE(od.PartNo),2)), CAST(CONVERT(VARCHAR(MAX), 
CAST(SUBSTRING(o.User_Memo1, CHARINDEX(@WorkC,o.User_Memo1)+@Chars, 8) AS 
DATE), 1) AS DATETIME)
ORDER BY od.OrderNo

Answer 1

您将BEGIN语句放错了位置

IF @WorkC = 'Cut'
BEGIN
  SET @WorkC2 = '%Cut%'
  SET @Chars = 4
END

Yough，您可以同时删除BEGIN和END。

IF @WorkC = 'Cut'
  SET @WorkC2 = '%Cut%'
  SET @Chars = 4

但是这通常是用一个CASE语句来完成的。

select 
    @WorkC2 = case 
                when @WorkC = 'Cut' then '%Cut%'
                when @WorkC = 'Frame' then '%Frame%'
                ...
              end

    ,@Chars = case 
                when @WorkC = 'Cut' then 4
                when @WorkC = 'Frame' then 6
                ...
              end

不过，您可以删除@WorkC并通过级联显式设置它，并节省大量的写作：

set @WorkC = '%' + @WorkC + '%' 

而且，更容易地设置@Chars too...note，您需要在之前执行该，然后在每一端添加%，如果在后面添加%，则设置-1

set @Chars = len(@WorkC) + 1

Answer 2

您需要将BEGIN放在IF语句中，如下所示：

IF @WorkC = 'Cut'   
BEGIN   
   SET @WorkC2 = '%Cut%'   
   SET @Chars = 4   
END

IF @WorkC = 'Frame'
BEGIN
  SET @WorkC2 = '%Frame%'
  SET @Chars = 6
END
...