Post reply

Passing blank parameter

  • October 15, 2009 at 4:11 pm

    #227142

    My code:

    Select Project.Status, Project.Client, Project.StartDate

    From Project

    Where (Project.Status IN (@parmStatus)) AND (Project.StartDate IN (@parmStartDate))

    Order By Project.Status

    -- What can I add to the "Where" line to allow blanks for Status and/or StartDate?

  • November 9, 2009 at 2:50 am

    #1076551

    You can modify your code to the below code:-

    Report level you need to check null value box,when you don't want to pass the values.

    when you don't pass the value it will take null value and in SQl null value will be passed and it will not use the where condition .So that all values will be fetched.

    Select Project.Status, Project.Client, Project.StartDate

    From Project

    Where Project.Status IN case (when @parmStatus is not null then @parmStatus

    when @parmStatus is null then Project.Status

    end )

    AND Project.StartDate IN case ( when (@parmStartDate) is not null then (@parmStartDate)

    when (@parmStartDate) is null then Project.StartDate

    end)

    Order By Project.Status

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply