Stored Procedure Question

  • I know that there is a way when declaring the variables at the start of a query to set something obsecure like -1 to equal all.

    The following is a sample of the code, I will obviously have more parameters and things that I filter on.

    Declare @memberid as Varchar (50)

    Set @Memberid = '999999999'

    Select * from dbo.tblClaims_eligible

    and  membid = @memberid

    What do I do if I want to pull back ALL data regardless of the member id?

  • On way:

    SELECT * FROM dbo.tblClaims_eligible

    WHERE (@memberid IS null OR membid = @memberid)

    If you pass @memberid as null the first half will always evaluate to true, thus it won't test the second half. Make sure you keep the brackets if you are using multiple parts like this, ie:

    WHERE (@param1 IS null OR col1 = @param1)

    AND (@param2 IS null OR col2 = @param2)

    ...

    AND (@paramN IS null OR colN = @paramN)

  • Try this:

    Declare @memberid as Varchar (50)

    Set @Memberid = '999999999'

    Select * from dbo.tblClaims_eligible

    where membid = isnull(@memberid,membid)

    Thanks

    Sreejith

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

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