You must Sign In to post a response.
  • Category: SQL Server

    Sql procedure using case in where clause

    hello friends,

    i am stuck in this sp hope someone will help me out.

    i have three text boxes : saleid,date,Lotnumber and a button SEARCH

    if SALEID+DATE then it is treated as salenumber
    if saleID only then saleid

    My code:


    ALTER PROCEDURE dbo.pr_GetLotDetails
    -- Add the parameters for the stored procedure here
    @Sale VARCHAR(50) = NULL,
    @Lotnumber VARCHAR(50) = NULL,
    @SaleDate DATETIME = NULL

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT ObjectID
    ,s.SaleID
    ,lc.CatalogueEntry as lottitle
    ,LotNumber
    ,ViewerDiscretionIND
    ,HideLot
    ,lc.RestrictedCountries as LotRestrictedCountries
    ,lc.AllowedCountries as LotAllowedCountries
    ,SaleRoomCode
    ,SaleCode
    ,SaleNumber
    ,SaleTitle
    ,SaleStartDate
    ,SaleEndDate
    ,Active
    ,RealTimeAuctionStatus
    ,ActivationDateTimeUTC
    ,s.RestrictedCountries
    ,s.AllowedCountries

    FROM LotsOnline.dbo.Sale s WITH(NOLOCK)
    INNER JOIN LotsOnline.dbo.LotCommon lc WITH(NOLOCK)
    ON s.SaleID=lc.SaleID
    ---where s.SaleID=@Sale
    --END
    --GO

    WHERE


    s.SaleID =
    CASE
    WHEN @Sale IS NULL
    THEN s.SaleID
    WHEN @Sale IS NOT NULL
    THEN @Sale
    WHEN @Sale IS NOT NULL AND @SaleDate IS NULL
    THEN @Sale
    --WHEN @Sale IS NOT NULL AND @SaleDate IS NOT NULL
    --THEN s.SaleNumber=@Sale

    END
    AND

    s.SaleNumber =
    CASE
    WHEN @Sale IS NOT NULL AND @SaleDate IS NOT NULL
    THEN @Sale
    ELSE s.SaleID

    END
    AND
    Lotnumber=
    CASE WHEN @Lotnumber IS NOT NULL
    THEN convert(varchar(50),@Lotnumber)
    ELSE convert(varchar(50),LotNumber)
    END

    AND
    isnull(SaleStartDate,'01/01/1900') = isnull(@SaleDate,SaleStartDate)
    --CASE WHEN @SaleDate IS NOT NULL
    -- THEN ISNULL(@SaleDate,'01/01/1900')
    -- ELSE SaleStartDate
    -- END

    PRINT N'Object Name: ' + @Sale
    PRINT N'Object Name: ' + @Lotnumber
    PRINT ISNULL(@SaleDate,'01/01/1900')



    END


    GO

    Above code is working fine for saleiD and saledate, but here for condition
    if SALEID+DATE then it is treated as salenumber but it is treated as saleid only :(

    And it is not working for saleid+lotnumber :(
    Plesae help
  • #763554
    Hi,

    It is too difficult to understand the requirement if you ask as per your own words(like your project), explore the requirement in unique way and easy to understand for everyone.

    In your case you say that you are concatenate saleId and date but it is not giving result as you want.

    I request you to please provide some sample result and your expected output from that, so that we can help you better.

    Hope you understand..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #763563
    Hello.

    The first thing, you can not use case statement in where clause.
    Instead, you need to use "And" / "OR" / "IN".

  • #763680
    Hai Niketa,
    I don't think you need to write this much long procedure for your conditions.
    You can keep the conditions upfront and get the where clause before getting the select statement like:

    declare @where varchar(max)
    set @where = ''

    Now you can check your condition and use this variable to set the values.
    Once all the three values are set, you can use the select statement and put this where clause as appended.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


Sign In to post your comments