Another option using a table valued function. In this example I use an outer join but if you only want the code '82' values an inner join would work as well.

/* Table valued function for parsing a delimited array into a table */

CREATE FUNCTION [dbo].[tvfParseDelimitedString]

(

@s-2 NVARCHAR(MAX) -- Delimited input string

,@Split CHAR(1) -- Delimiter used for the input string

)

RETURNS @Table TABLE

(

[ID] INT NOT NULL IDENTITY(1,1)

,[Value] NVARCHAR(MAX) NULL

,PRIMARY KEY ([ID])

,UNIQUE ([ID])

)

BEGIN

DECLARE @X XML

SET @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')

INSERT INTO @Table

SELECT LTRIM(T.c.value('.','NVARCHAR(MAX)')) AS [Value]

FROM @X.nodes('/root/s') T (c)

RETURN

END

/* Create a set of test data */

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (ProdGrpCode INT)

INSERT INTO #TempTable

SELECT '500' UNION SELECT '510' UNION SELECT '5201' UNION

SELECT '580' UNION SELECT '630' UNION SELECT '460' UNION

SELECT '470' UNION SELECT '480' UNION SELECT '490' UNION

SELECT '777' UNION SELECT '888' UNION SELECT '999'

/* The final query that takes the delimited input */

/* and assigns the proper code */

DECLARE @myVar AS NVARCHAR(50)

SET @myVar = N'500, 510, 5201, 580, 630, 460, 470, 480, 490'

SELECT

ProdGrpCode

,(CASE

WHEN ProdGrpCode = Value THEN 82

ELSE 99

END) AS Code

FROM

#TempTable AS t

LEFT OUTER JOIN

(SELECT Value FROM dbo.tvfParseDelimitedString(@myVar,',')) AS v

ON t.ProdGrpCode = v.Value

Output:

[font="Courier New"]ProdGrpCodeCode

46082

47082

48082

49082

50082

51082

5201 82

58082

63082

77799

88899

99999[/font]