Stored Procedure Best Practice

[Leon]

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!

 

[Karl Seguin]

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

 

[Eric Sabine]

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

 

[Guest]

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

 

[Scott Allen]

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.

 

[Guest]

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.