conditional operator

Question

conditional operator

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions

Igor Makedon Ten Centuries Points: 1235 More actions · Answer 3

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

habboub Old Hand Points: 330 More actions · Answer 4

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]
@userid 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

MeThinks SSC Eights! Points: 821 More actions · Answer 5

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

ALTER PROCEDURE [dbo].[Search]

@userid 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