CODEWITHSUNDEEP
sql-servert-sql
user1452574
user1452574

Reputation: 485

How to declare multiple variables inside IF statement blocks?

So what I have below works great if I remove all the SET @Chars lines, somehow, the @Chars is not being picked up. Tested by just setting variables directly and testing 1 by 1, so the code is fine, something wrong in the variable setting part. Any tips are greatly appreciated, thanks

Here's my code:

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

Upvotes: 1

Views: 240

Answers (2)

S3S
S3S

Reputation: 25132

You are misplacing the BEGIN statement

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

Yough, you could remove the BEGIN and END all together.

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

But this is usually this is done with a CASE statement much more cleanly.

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

Though, you could remove the @WorkC and set it explicitly with concatenation and save a LOT of writing: 

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

And, set @Chars more easily too...note you need to do this before you add the % on each end, or make it -1 if you do it after

set @Chars = len(@WorkC) + 1

Upvotes: 1

Rick S
Rick S

Reputation: 6586

You need to put the BEGIN inside your IF statements, like this:

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

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

Upvotes: 1

Related Questions