473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Stored Procedure Best Practice

I have a situation in which I need two stored procedures that do basically
the same thing,
but takes in a different parameter value and return less of the same data. I
wonder is it best to
do two different stored procedures or just one to accomplish my goal?

The first sp take in the @emailaddress parameter value and selects the
following values from two different table:
Username, Password, FirstName, LastName

for completeness here the full second sp....

CREATE PROCEDURE GetStudentDetail
@AccountID INT
AS
SELECT A.Username, A.Password, A.SecretQuestion, A.SecretAnswer,
A.RoleID, A.Active, S.FirstName, S.LastName,
S.BirthMonth, S.BirthDay, S.BirthYear, S.Gender, S.SchoolState,
S.SchoolName, S.Classification, S.Major, S.EmailAddress

FROM Account A INNER JOIN Student S ON A.AccountID = S.AccountID
WHERE A. AccountID = @AccountID
GO

Can I accomplish my goal in a better more efficient way or do I need two
stored procedures?

Thanks!
Nov 18 '05 #1
5 2318
Leon,
There is no good answer and its something we've all struggled with. Your
three choices are:

- Write 2 sprocs

- Write 1 sproc and have 2 statements (using IF)
IF @AccountId <> 0 BEGIN

END ELSE IF @emailAddress <> '' BEGIN

END
-Write 1 sproc and have a single statement (using either dynamic sql, or
index-unfriendly code):
SELECT * FROM blah
where (@AccountId = 0 OR AccountId = @AccountId) AND (@emailAddress = ''
OR emailAddress = @EmailAddress)
The last solution sucks for indexes, though you could use dynamic sql
instead which sucks for other things.

A forth choice would be to use an O/R mapper with dynamic sql capabilities,
such as http://www.ormapper.net/ but that's a pretty big jump and might not
be suited to your situation.
If you aren't willing to look at an O/R mapper, my preference is to use the
1st method...more code, but cleaner and easier to optimize

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Leon" <vn*****@msn.com> wrote in message
news:um**************@TK2MSFTNGP12.phx.gbl...
I have a situation in which I need two stored procedures that do basically
the same thing,
but takes in a different parameter value and return less of the same data. I wonder is it best to
do two different stored procedures or just one to accomplish my goal?

The first sp take in the @emailaddress parameter value and selects the
following values from two different table:
Username, Password, FirstName, LastName

for completeness here the full second sp....

CREATE PROCEDURE GetStudentDetail
@AccountID INT
AS
SELECT A.Username, A.Password, A.SecretQuestion, A.SecretAnswer,
A.RoleID, A.Active, S.FirstName, S.LastName,
S.BirthMonth, S.BirthDay, S.BirthYear, S.Gender, S.SchoolState,
S.SchoolName, S.Classification, S.Major, S.EmailAddress

FROM Account A INNER JOIN Student S ON A.AccountID = S.AccountID
WHERE A. AccountID = @AccountID
GO

Can I accomplish my goal in a better more efficient way or do I need two
stored procedures?

Thanks!

Nov 18 '05 #2
If you go with 1 sproc your proc will become a victim of parameter sniffing
and thus you would likely end up with a less desirable execution plan for
one of the two separate statements. Test it. But basically, err on the
side of performance. Go for 2.

hth
Eric

Leon wrote:
I have a situation in which I need two stored procedures that do
basically the same thing,
but takes in a different parameter value and return less of the same
data. I wonder is it best to
do two different stored procedures or just one to accomplish my goal?

The first sp take in the @emailaddress parameter value and selects
the following values from two different table:
Username, Password, FirstName, LastName

for completeness here the full second sp....

CREATE PROCEDURE GetStudentDetail
@AccountID INT
AS
SELECT A.Username, A.Password, A.SecretQuestion, A.SecretAnswer,
A.RoleID, A.Active, S.FirstName, S.LastName,
S.BirthMonth, S.BirthDay, S.BirthYear, S.Gender, S.SchoolState,
S.SchoolName, S.Classification, S.Major, S.EmailAddress

FROM Account A INNER JOIN Student S ON A.AccountID = S.AccountID
WHERE A. AccountID = @AccountID
GO

Can I accomplish my goal in a better more efficient way or do I need
two stored procedures?

Thanks!

Nov 18 '05 #3
Karl, I just posted on the sql forum a very similar question to Leon's, but
on the update side: I'd like to write a single sp to handle any/all updates
to a table, make the parameters optional, and in the cases where I don't pass
the parameters then leave those columns alone. Is that equally better served
by writing separate sp's for all the combinations of update fields? Right now
I'm doing it all via dynamic sql, but am guessing I need to move to sp's to
have any chance to scale up.

Thanks,
Bill

"Karl Seguin" wrote:
Leon,
There is no good answer and its something we've all struggled with. Your
three choices are:

- Write 2 sprocs

- Write 1 sproc and have 2 statements (using IF)
IF @AccountId <> 0 BEGIN

END ELSE IF @emailAddress <> '' BEGIN

END
-Write 1 sproc and have a single statement (using either dynamic sql, or
index-unfriendly code):
SELECT * FROM blah
where (@AccountId = 0 OR AccountId = @AccountId) AND (@emailAddress = ''
OR emailAddress = @EmailAddress)
The last solution sucks for indexes, though you could use dynamic sql
instead which sucks for other things.

A forth choice would be to use an O/R mapper with dynamic sql capabilities,
such as http://www.ormapper.net/ but that's a pretty big jump and might not
be suited to your situation.
If you aren't willing to look at an O/R mapper, my preference is to use the
1st method...more code, but cleaner and easier to optimize

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Leon" <vn*****@msn.com> wrote in message
news:um**************@TK2MSFTNGP12.phx.gbl...
I have a situation in which I need two stored procedures that do basically
the same thing,
but takes in a different parameter value and return less of the same data.

I
wonder is it best to
do two different stored procedures or just one to accomplish my goal?

The first sp take in the @emailaddress parameter value and selects the
following values from two different table:
Username, Password, FirstName, LastName

for completeness here the full second sp....

CREATE PROCEDURE GetStudentDetail
@AccountID INT
AS
SELECT A.Username, A.Password, A.SecretQuestion, A.SecretAnswer,
A.RoleID, A.Active, S.FirstName, S.LastName,
S.BirthMonth, S.BirthDay, S.BirthYear, S.Gender, S.SchoolState,
S.SchoolName, S.Classification, S.Major, S.EmailAddress

FROM Account A INNER JOIN Student S ON A.AccountID = S.AccountID
WHERE A. AccountID = @AccountID
GO

Can I accomplish my goal in a better more efficient way or do I need two
stored procedures?

Thanks!


Nov 18 '05 #4
Bill:

I've had good performance using queries like this:

UPDATE Customers

SET
CompanyName = COALESCE(@CompanyName, CompanyName),
ContactName = COALESCE(@ContactName, ContactName)

WHERE CustomerID = @CustomerID
If a NULL is passed for one of the parameters, the value already in
the table is used. I'd want to test this for performance a little more
carefully if using it to update more than one record at a time.

Really, it's putting OR login into WHERE clauses that can kill
performance, especially with optional params. They can also hurt in
GROUP BY and ORDER BY because the execution plans should be entirely
different depending on what was actually passed to the proc.

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Thu, 21 Oct 2004 10:09:10 -0700, Bill Borg
<Bi******@discussions.microsoft.com> wrote:
Karl, I just posted on the sql forum a very similar question to Leon's, but
on the update side: I'd like to write a single sp to handle any/all updates
to a table, make the parameters optional, and in the cases where I don't pass
the parameters then leave those columns alone. Is that equally better served
by writing separate sp's for all the combinations of update fields? Right now
I'm doing it all via dynamic sql, but am guessing I need to move to sp's to
have any chance to scale up.

Thanks,
Bill


Nov 18 '05 #5
Scott, thanks a lot. I got a similar response to use "coalesce" from my post
on the sql group, and am anxious to check it out. It doesn't "seem" (famous
last words) like it should be a performance hog since I've already got the
right row in my hands, but I'll put it through the paces.

"Scott Allen" wrote:
Bill:

I've had good performance using queries like this:

UPDATE Customers

SET
CompanyName = COALESCE(@CompanyName, CompanyName),
ContactName = COALESCE(@ContactName, ContactName)

WHERE CustomerID = @CustomerID
If a NULL is passed for one of the parameters, the value already in
the table is used. I'd want to test this for performance a little more
carefully if using it to update more than one record at a time.

Really, it's putting OR login into WHERE clauses that can kill
performance, especially with optional params. They can also hurt in
GROUP BY and ORDER BY because the execution plans should be entirely
different depending on what was actually passed to the proc.

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Thu, 21 Oct 2004 10:09:10 -0700, Bill Borg
<Bi******@discussions.microsoft.com> wrote:
Karl, I just posted on the sql forum a very similar question to Leon's, but
on the update side: I'd like to write a single sp to handle any/all updates
to a table, make the parameters optional, and in the cases where I don't pass
the parameters then leave those columns alone. Is that equally better served
by writing separate sp's for all the combinations of update fields? Right now
I'm doing it all via dynamic sql, but am guessing I need to move to sp's to
have any chance to scale up.

Thanks,
Bill


Nov 18 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: tommynospam | last post by:
I've recently inhereted an environment in which we have over 600 stored procedures. The documentation is either very poor or non-existant and am needing advice on how to determine if a stored...
4
by: erich93063 | last post by:
I have a stored procedure which is performing a search against a "task" table. I would like to pass in a variable called @strAssignedTo which will be a comma delimeted list of employee ID's ie:...
2
by: Mike Hutton | last post by:
I have a rather odd problem. I have a SP which uses temp. tables along the way, and then returns a table of results: CREATE PROCEDURE dbo.usp_myproc( @pNameList VARCHAR(6000) ) AS
2
by: Justin | last post by:
I am creating a web app for a client in VS.NET using ASP.NET with C#. I need to query three tables in a database using one parameter and display the results on the page. my question is should I use...
1
by: Leon | last post by:
I have a situation in which I need two stored procedures that do basically the same thing, but takes in a different parameter value and return less of the same data. I wonder is it best to do two...
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
3
by: CK | last post by:
Hi All, Quick question, I have always heard it best practice to check for exist, if so, drop, then create the proc. I just wanted to know why that's a best practice. I am trying to put that theory...
3
by: GaryDean | last post by:
In a new 2.0 website I am developing I am using .XSD tableadapters, building them with the wizard), and choosing the Stored Procedure option. I now notice that when I go back to change the sql...
2
by: Krij | last post by:
Hi, I'm a student and I have the following working example that troubles me (in SQL Server 2005): CREATE PROCEDURE dbo.CustomersOrderHistory ( @Firstname varchar(7) OUTPUT) AS SELECT ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.