Ok. Based on what presented. I'm guessing, there is a parameter like '456,678' in the Stored Procedure, and you want to put the parameter into the query and expect the query to return the rows with CPTCode 456 and 678? If the the case, here is an example.
DECLARE @temp TABLE (
Facility INT IDENTITY(1,1),
CPTCode VARCHAR(30),
Something VARCHAR(50)
)
INSERT INTO @temp
SELECT 45, 'Apple' UNION
SELECT 46, 'Orange' UNION
SELECT 456, 'Pear' UNION
SELECT 577, 'Banana' UNION
SELECT 678, 'Strawberry'
DECLARE @CptCode VARCHAR(30)
SET @CptCode = '45,456,577'
DECLARE @cptCodeRows TABLE(CPTCode VARCHAR(30));
;WITH CTE_CPTCode
AS
(
SELECT [xml_val] = CAST('<b>' + REPLACE(@CptCode ,',','</b><b>') + '</b>' AS XML)
)
INSERT INTO @cptCodeRows
SELECT
ProgramId = col.value('.','VARCHAR(30)')
FROM CTE_CPTCode
CROSS APPLY [xml_val].nodes('/b') CA(col)
SELECT * FROM @temp t JOIN @cptCodeRows c
ON t.CPTCode = c.CPTCode
Output:
Facility CPTCode Something CPTCode
1 45 Apple 45
3 456 Pear 456
4 577 Banana 577