USE tempdb

GO

DROP TABLE Product

CREATE TABLE Product (
  ID             INT   IDENTITY ( 1 , 1 ),
  EncodedProduct VARCHAR(7))

INSERT INTO Product
           (EncodedProduct)
SELECT '0101001'
UNION ALL
SELECT '0201001'
UNION ALL
SELECT '0301001'
UNION ALL
SELECT '0301002'
UNION ALL
SELECT '0102001'
UNION ALL
SELECT '0202001'
UNION ALL
SELECT '0302001'
UNION ALL
SELECT '0303001'
UNION ALL
SELECT '0401001'
UNION ALL
SELECT '0504001'
UNION ALL
SELECT '0101010'
UNION ALL
SELECT '0102010'
UNION ALL
SELECT '0103010'
UNION ALL
SELECT '0203010'

--This fills your PrdCode combo
Select distinct PrdCode from (
SELECT substring(EncodedProduct,1,2) AS PrdCode,
	   Substring(EncodedProduct,3,2) AS PrdClass,
	   Substring(EncodedProduct,5,3) AS PrdSubClass
FROM   Product) as Prd

GO


--This fills your PrdClass combo based on the PrdCode selection (@PrdCode)
Declare @PrdCode varchar(2)
set @PrdCode = '01'

select distinct PrdClass from (
SELECT substring(EncodedProduct,1,2) AS PrdCode,
	   Substring(EncodedProduct,3,2) AS PrdClass,
	   Substring(EncodedProduct,5,3) AS PrdSubClass
FROM   Product) as Prd
where PrdCode = @PrdCode

GO

Declare @PrdCode varchar(2),
		@PrdClass varchar(2)

SET @PrdCode = '03'
SET @PrdClass = '01'


select distinct PrdSubClass from (
SELECT substring(EncodedProduct,1,2) AS PrdCode,
	   Substring(EncodedProduct,3,2) AS PrdClass,
	   Substring(EncodedProduct,5,3) AS PrdSubClass
FROM   Product) as Prd
where PrdCode = @PrdCode
AND PrdClass = @PrdClass