NULL in Report Parameter

• ry.sqldev

  SSC Eights!

  Points: 882

  More actions

  May 31, 2011 at 5:15 pm

  #241350

  please find the query below and for ClaimStatus report parameter i m supplying available values those are Pay,Paid. i should get all(both paid and pay) status records WHEN I select NULL in report parameter.

  i am converting this report from crystal to ssrs 2008.in crystal even if we supply paramenter values also wewill get NULL check box and when we select that it displays all. now i need same functionality.what can i include in where conditon's status statement

  @startOKdatetime

  ,@endOKdatetime

  , @ClaimStatus CHAR(10)

  AS

  --set @startOK = '2/1/2011'

  --set @endOK= '5/26/2011'

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  -- Remove time part of parameters

  SET @startOK = CAST(CONVERT(VARCHAR, @startOK, 101) AS DATETIME)

  SET @endOK = CAST(CONVERT(VARCHAR, @endOK, 101) AS DATETIME) + 1

  SELECT c.id, reason, okpayby, cd.servcode, cd.amt, cd.paidamt, cd.amountpaid,

  ct.description as contract, b.description as benefit, c.status

  FROM claim c

  JOIN claimdetail cd on cd.claimid = c.claimid

  JOIN benefit b on b.benefitid = cd.benefitid

  JOIN contract ct on ct.contractid = cd.contractid

  WHERE ((c.status IN ('PAID','PAY')) and c.formtype = '2000'

  and c.resubclaimid = ''

  and okpaydate between @startOK and @endOK

  Thanks

• sdvoranchik

  SSCrazy

  Points: 2800

  More actions

  June 1, 2011 at 5:49 am

  #1332448

  You should be able to add an OR to account for the NULL in the stored proc parameter

  WHERE ((c.status IN ('PAID','PAY') OR @ClaimStatus IS NULL)