IN Statement multivalue

[jamesbon007]

hi all,

I have developed sql statement which i will be using later in reporting services 2008. its parameter based where user can select Year, Month, Proc, Diag to get trust activity. The SQL statement takes into account multivalue's using IN statement for @FinMonth. How do i do the same for @Proc and @Diag.

currently the data for Procedure codes and Diagnosis code is a string.
e.g x45.6,c456, r54.9 etc

My Reporting services will have a picklist to look up the codes but i would like to add more than one code... so my parameters for @Proc or Diag can accept more codes. The SQL statement only takes into acount one code. How do i change this?

SELECT     S.crn, S.admission, P.Procedures, D.Diagnoses, S.DIS_DATE, S.patients_RowKey, S.ADM_TYPE_DESC, S.[DIS HOSP DESC], S.[DIS SPEC DESC], 
                      S.DIS_DATE_FISCAL_MONTH_NAME, S.DIS_DATE_FISCAL_MONTH, S.DIS_DATE_FISCAL_YEAR
FROM         inf.tbl_PS_IXP_MEDREC_PROCS_FLAT AS P INNER JOIN
                      inf.tbl_PS_IXP_ADMS_AND_DIS AS S ON P.Crn = S.crn AND P.admission = S.admission INNER JOIN
                      inf.tbl_PS_IXP_MEDREC_DIAG_FLAT AS D ON S.crn = D.Crn AND S.admission = D.admission
WHERE     (S.DIS_DATE_FISCAL_YEAR = @FinYear) AND (S.DIS_DATE_FISCAL_MONTH_NAME IN (@FinMonth)) AND (P.Procedures LIKE '%' + @Proc + '%') AND 
                      (D.Diagnoses LIKE '%' + @Diag + '%')
ORDER BY S.DIS_DATE

Basically how do i add an IN statment using LIKE %

 

[Qik3Coder]

1.) Create a user defined function(udf) that returns table
1.a)This function should take a comma separatated value
1.b)This function should return a single column with said values in it.
2.) Join to this new udf, passing in the parameter in question
2.a) join on value like


pseudo code:
Declare @_myParms varchar(8000)
select @_myParms = 'stuff, and things, and other things'

select *
from dbo.tableA a
JOIN dbo.udfMyCSVShredder(@_myParms) as b ON
a.col1 LIKE '%'+b.col1+'%'
where a.col2 = 'X'

There are several FAQs on creating udfs and splitting comma separated values.

HTH,
Lodlaiden


I'll answer your question, not solve your problem