March 6, 2008 at 6:42 am
Heh... Daniel, unless you're wearing a porkchop around your neck, everyone is welcome in the "lions pit". Nice code...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 7:09 am
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
Change is inevitable... Change for the better is not.
March 6, 2008 at 8:35 am
TicketID = CASE WHEN TicketID > 0 THEN @TicketID ELSE TicketID END
March 6, 2008 at 9:51 am
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
Abd
March 6, 2008 at 10:32 am
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