Re: Writing ONE STORED PROCEDURE rather than 5
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 03/01/05
- Next message: mk: "Re: De-dupe question"
- Previous message: Max: "value pair database design I need help with"
- In reply to: pmud: "Writing ONE STORED PROCEDURE rather than 5"
- Next in thread: Ross Presser: "Re: Writing ONE STORED PROCEDURE rather than 5"
- Reply: Ross Presser: "Re: Writing ONE STORED PROCEDURE rather than 5"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 1 Mar 2005 11:22:31 -0600
Therea re two possible solutions to this. One was posted by Celko and Mesa,
but the other, depending on your performance situation, migh be better:
create procedure invoices$get
(
@orderNo int = null,
@Status varchar(30) = null ,
@StartDate DateTime = null ,
@EndDate DateTime = null ,
@Store varchar(30) = null
)
as
if @orderNo is null and @status is null and @startDate is null and @endDate
is null and @store is null
begin
exec usp_viewAll
end
else if @orderNo is NOT null and @status is null and @startDate is null and
@endDate is null and @store is null
begin
exec usp_order @orderNo = @orderNo
end
...other cases
else
exec invoices$getGeneric @orderNo = @orderNo, @status = @status,
@startDate = @startDate, @endDate = @endDate, @store= @store
Then you might have a call to a very general procedure like the one
suggested. I would go with the other solution first, and do this only when
the very generic procedure fails to be fast enough. The problem is that
sometimes the very generic procs cannot use a reasonable index, so you can
optimize certain cases like this.
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Blog - http://spaces.msn.com/members/drsql/ Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "pmud" <pmud@discussions.microsoft.com> wrote in message news:CA70ED34-C943-4060-9A6B-9E57AC6E2299@microsoft.com... > Hi, > > I have created the following 5 stored procedures which return rows based > on > the user input... > > Now what i need to know is that is there a way of ....rather than writing > 5 > stored procedures....I can merge all these into a single stored > procedure.... > so that when the user doesnt enter any one or more of the input values, it > lets those parameters remain null & selects only the rows for which a > value > is input by the user??? > > My stored procedures are: > 1. CREATE PROCEDURE [dbo].[usp_ViewAll] AS > select * from INVOICES > GO > > > 2. CREATE PROCEDURE dbo.usp_Order (@OrderNo int) AS > select * from INVOICES where ORDER_NO = @OrderNo > GO > > > 3. CREATE PROCEDURE [dbo].[usp_Status] (@Status varchar(30) )AS > SELECT * FROM INVOICES > WHERE CASE @Status WHEN 'Open' THEN OPEN_STATUS > WHEN 'In Process' THEN INPROCESS_STATUS > WHEN 'Completed' THEN COMPLETED_STATUS > END = 'T' ; > > GO > > > 4. CREATE PROCEDURE dbo.usp_Date (@StartDate DateTime , @EndDate > DateTime > )AS > Select * from INVOICES where ORDER_DATE between @StartDate AND @EndDate > GO > > > 5. CREATE PROCEDURE [dbo].[usp_Store] (@Store varchar(30) ) AS > select * from INVOICES where CUST_CODE=(select CUST_CODE from CUSTOMER > where > COMPANY_CODE = @Store) > GO > > Any help is appreciated... > > Thanks > > > -- > pmud
- Next message: mk: "Re: De-dupe question"
- Previous message: Max: "value pair database design I need help with"
- In reply to: pmud: "Writing ONE STORED PROCEDURE rather than 5"
- Next in thread: Ross Presser: "Re: Writing ONE STORED PROCEDURE rather than 5"
- Reply: Ross Presser: "Re: Writing ONE STORED PROCEDURE rather than 5"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
