conditional operator

  • Heh... Daniel, unless you're wearing a porkchop around your neck, everyone is welcome in the "lions pit". Nice code...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I agree.... the Dynamic SQL will frequently perform better because it snuffs any chance of parameter sniffing and forces a recompile so you're always using a good execution plan... For short code, the recompile doesn't take long. Of course, if you hit the code a couple thousand times a second, the recompiles will become a problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • TicketID = CASE WHEN TicketID > 0 THEN @TicketID ELSE TicketID END

  • Ok guys, you are the experts

    this is my full sp, now you can tell me if is it worth keeping it as it is in the sql server, or use it as dynamic sql in my web pages?

    thanks

    ALTER PROCEDURE [dbo].[Search]

    @user-id nvarchar(10),

    @TicketID int,

    @Type nvarchar(80),

    @Priority nvarchar(20),

    @FromDate datetime,

    @ToDate datetime

    AS

    BEGIN

    SELECT TicketID, UserID, Title, Status, Type, Priority, CreationDate

    FROM Ticket

    WHERE UserID=@UserID

    AND (CASE WHEN @TicketID IS NULL THEN 1 WHEN @TicketID =0 THEN 1 WHEN TicketID=@TicketID then 1 else 0 END=1)

    AND (CASE WHEN @Type IS NULL THEN 1 WHEN Type=@Type then 1 else 0 END=1)

    AND (CASE WHEN @Priority IS NULL THEN 1 WHEN Priority=@Priority then 1 else 0 END=1)

    AND (CASE WHEN (@FromDate IS NULL) OR (@ToDate IS NULL) THEN 1 WHEN (CreationDate between @FromDate AND (@ToDate+1)) then 1 else 0 END=1)

    END


    Kindest Regards,

    Abd

  • Throwing myself in the lions pit :), I would write something like this

    ALTER PROCEDURE [dbo].[Search]

    @user-id nvarchar(10),

    @TicketID int,

    @Type nvarchar(80),

    @Priority nvarchar(20),

    @FromDate datetime,

    @ToDate datetime

    AS

    BEGIN

    SELECT TicketID, UserID, Title, Status, Type, Priority, CreationDate

    FROM Ticket

    WHERE UserID=@UserID

    AND (@TicketID IS NULL OR @TicketID =0 OR TicketID=@TicketID)

    AND (@Type IS NULL OR Type=@Type)

    AND (@Priority IS NULL OR Priority=@Priority)

    AND (@FromDate IS NULL OR @ToDate IS NULL OR CreationDate between @FromDate AND DateAdd(DD, 1, @ToDate)

    END

Viewing 5 posts - 16 through 19 (of 19 total)

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