Which is best way
-
hi to all,
i have a little confusion in my company there are lot of if statements in a proc but they say it will improve the performance, but maintain the code is too tough, Ok i will explain my problem here, this is my problem i need to create a proc which return the coresponding record for the id if the id is 0 then it will return all the records now the code they write
CREATE PROC TestProc (@id INT)
AS
BEGIN
IF (@id = 0)
SELECT * FROM TableA
ELSE
SELECT * FROM TableA WHERE id = @id
END
now i change the code like this
CREATE PROC TestProc1 (@id INT)
AS
BEGIN
SELECT * FROM TableA
WHERE id = @id OR @id = 0
END
from my code i am not see anything changed in planwise or IO wise no change, so will you please tell me
which is the best way to create this proc, using the if else or my code
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Try this
CREATE PROC TestProc1 (@id INT)
AS
BEGIN
if(@id=0) --if you pass null then no need to write this code instead of 0 for all recods from Code behind
set @id=null
SELECT * FROM TableA
WHERE id = coalesce(@id ,id)
END
-
which is the best way to create this proc, using the if else or my code
Your COde
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
I suppose its an 'it depends' answer, your code is ok if you have simple queries, however when you get into complex queries with Multiple paramater combinations it will become a maintenance nightmare.
Consider this how would you write the code if the sp received 2 paramaters, where both, none or one of them can be set, would you create a nested if for each combination?
Would you write something like this (it probably doesnt compile its sample)
CREATE PROC TestProc (@id INT,@name Varchar(100))
AS
BEGIN
IF (@id is Null and @name is Not NULL)
Begin
SELECT *
FROM TableA
Where name=@name
end
ELSE IF (@id = is NULL and @name is NULL)
Begin
SELECT * FROM TableA
end
ELSE If (@id is NOT NULL and @name is NOT NULL)
Begin
SELECT * FROM TableA
WHERE id = @id
AND name=@name
end
ELSE
Begin
SELECT * FROM TableA
WHERE id = @id
end
END
Overkill isnt it.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
thava (11/15/2012)
which is the best way to create this proc, using the if else or my codeNeither.
If, else - http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Your code - http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
that's awesome, i didn't expect that links
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Jason-299789 (11/16/2012)
I suppose its an 'it depends' answer, your code is ok if you have simple queries, however when you get into complex queries with Multiple paramater combinations it will become a maintenance nightmare.Consider this how would you write the code if the sp received 2 paramaters, where both, none or one of them can be set, would you create a nested if for each combination?
Would you write something like this (it probably doesnt compile its sample)
CREATE PROC TestProc (@id INT,@name Varchar(100))
AS
BEGIN
IF (@id is Null and @name is Not NULL)
Begin
SELECT *
FROM TableA
Where name=@name
end
ELSE IF (@id = is NULL and @name is NULL)
Begin
SELECT * FROM TableA
end
ELSE If (@id is NOT NULL and @name is NOT NULL)
Begin
SELECT * FROM TableA
WHERE id = @id
AND name=@name
end
ELSE
Begin
SELECT * FROM TableA
WHERE id = @id
end
END
Overkill isnt it.
this is what i exactly told i think this is enough
ALTER PROC TestProc (@id INT, @aName VARCHAR(10))
AS
BEGIN
SELECT * FROM TableA
WHERE
(id = @id OR @id IS NULL ) AND
(AName = @aName OR @aName IS NULL)
END
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (11/16/2012)
this is what i exactly told i think this is enoughALTER PROC TestProc (@id INT, @aName VARCHAR(10))
AS
BEGIN
SELECT * FROM TableA
WHERE
(id = @id OR @id IS NULL ) AND
(AName = @aName OR @aName IS NULL)
END
Working, sure. Performing, no. Please read the links I posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/16/2012)
thava (11/15/2012)
which is the best way to create this proc, using the if else or my codeNeither.
If, else - http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Your code - http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
I've gotta take time to say it... I absolutely love the "Catch-All_Queries" article on your Web site, Gail. So far as I'm concerned, it's the defining article on the subject.
--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) -
Very cool!!!
living and learning !!!!
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
CREATE PROCEDURE SearchHistory_Dynamic (@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)
AS
DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
I remember a 'crazy' package in Oracle (dynamic: SELECT, INSERT, DELETE, UPDATE) !!
Thank´s
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply