0

I am trying to pass a string of ICD9 codes to a "WHERE IN TSQL clause" but it does not work. Can the below code be used with a few changes? Also passing a table name does not appear to work either. Any suggestions are appreciated. 

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @ICD9 VARCHAR(MAX)
DECLARE @TABLE
SET @StartDate = '2014-01-01 00:00:00' 
SET @EndDate = '2015-12-31 23:59:59'
SET @TABLE =  dbo.IPOP
SET @ICD9 = '284.1', '284.2', '280.0', '280.1'

SELECT X.* 
INTO @TABLE
FROM 
(SELECT c.ID, ip.AdmitDateTime, ip.ICD9Code, ip.ICD9Description
    FROM dbo.Inpatient ip
        INNER JOIN dbo.COHORT c ON (c.ID = ip.ID)
        WHERE ip.ICD9Code IN (@ICD9)
            AND ip.AdmitDateTime between @StartDate and @EndDate   

UNION ALL

SELECT c.ID, op.AdmitDateTime, op.ICD9Code, op.ICD9Description
    FROM dbo.Outpatient op
        INNER JOIN dbo.COHORT c ON (c.ID = op.ID)
        WHERE op.ICD9Code IN (@ICD9)
            AND op.AdmitDateTime between @StartDate and @EndDate
) X
ORDER BY ID, AdmitDateTime
GO
G83
  • 89
  • 1
  • 7

1 Answers1

1

The short answer is that you cannot pass a string delimited list to your IN clause.

Please refer to Erland Sommarskog's series of articles on the topic for alternatives:

Arrays and Lists in SQL Server

etliens
  • 1,352
  • 9
  • 16