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! 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!
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!
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!
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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:...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |