Home How to declare multiple variables inside IF statement blocks?
Reply: 0

How to declare multiple variables inside IF statement blocks?

user2045
1#
user2045 Published in July 17, 2018, 7:24 pm

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 login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.323552 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO