How to declare multiple variables inside IF statement blocks?

Question

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

You need to put the BEGIN inside your IF statements.

Rick S
– Rick S

2018-04-16 19:44:08 +00:00
Commented Apr 16, 2018 at 19:44 — Rick S
– Rick S, Commented Apr 16, 2018 at 19:44

S3S · Accepted Answer · 2018-04-16 19:53:52Z

1

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

edited Apr 16, 2018 at 19:53

answered Apr 16, 2018 at 19:44

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1452574 Over a year ago

Thanks, just knocked out a bunch of code with your tips

Rick S · Accepted Answer · 2018-04-16 19:48:55Z

1

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
...

answered Apr 16, 2018 at 19:48

Rick S

6,5966 gold badges32 silver badges43 bronze badges

1 Comment

user1452574 Over a year ago

I knew it had to be something simple, now I know for next time, although I just eliminated my ifs altogether thanks to tips from scsimon

Collectives™ on Stack Overflow

How to declare multiple variables inside IF statement blocks?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related