November 15, 2012 at 10:22 pm
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]
November 16, 2012 at 12:53 am
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
November 16, 2012 at 12:59 am
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;-)
November 16, 2012 at 1:18 am
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
November 16, 2012 at 1:44 am
thava (11/15/2012)
which is the best way to create this proc, using the if else or my code
Neither.
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 recoverability
November 16, 2012 at 7:33 am
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]
November 16, 2012 at 7:42 am
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]
November 16, 2012 at 8:48 am
thava (11/16/2012)
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
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 recoverability
November 16, 2012 at 9:27 am
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
Change is inevitable... Change for the better is not.
November 16, 2012 at 10:09 am
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