Must Declare Scalar Variable Error with IN Clause ?

• jbalbo

  SSCertifiable

  Points: 7580

  More actions

  February 13, 2014 at 9:08 am

  #289532

  Hi

  I am using a multiselect parameter @DiagnosisType in SSRS report

  My sp states ...

  ALTER PROCEDURE [dbo].[..._count]

  @StartDate datetime,

  @EndDate datetime,

  @Costcenter varchar(3),

  @DiagnosisType varchar(100)

  AS

  SELECT .....

  where (dbo.ClinicalType.Code in (@DiagnosisType))

  Any ideas would be great ...

  Thanks

  Joe

• KS

  Hall of Fame

  Points: 3323

  More actions

  February 13, 2014 at 9:41 am

  #1688834

  Try below

  ALTER PROCEDURE [dbo].[..._count]

  @StartDate datetime,

  @EndDate datetime,

  @Costcenter varchar(3),

  @DiagnosisType varchar(100)

  AS

  SELECT .....

  --where (dbo.ClinicalType.Code in (@DiagnosisType))

  where CHARINDEX(LTRIM(RTRIM(dbo.ClinicalType.Code)),@DiagnosisType) > 0

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  February 13, 2014 at 9:52 am

  #1688838

  That's not the best option. SSRS has an option to send the parameters in a way that can be evaluated as necessary within the IN clause.

  I don't remember how to do it, but you can search for that.

  If you prefer to do it all in T-SQL, you could use a splitter to split your variable. The best one using T-SQL is over here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  And you could use it like this:

  CROSS APPLY dbo.DelimitedSplit8K(@DiagnosisType, ',') split

  WHERE ClinicalType.Code = split.Item

  OR

  WHERE ClinicalType.Code IN (SELECT Item FROM dbo.DelimitedSplit8K(@DiagnosisType, ','))

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• jbalbo

  SSCertifiable

  Points: 7580

  More actions

  February 13, 2014 at 10:03 am

  #1688842

  Thanks for the info

  I am going to try that

  I did get around it by including the data in the SP without the where then using the filter on the Tablix , just not sure if its the best way....