help need
-
hi,
i have table with following feilds
ProgramId ProgramType AccountName ProgramName ProgramBudget ProgramStatus
I need to write a search query to generate a report where search criteria will be (ProgramType=1,2,3 or All of above),(AccountName=a,b,c or All of above),(ProgramStatus=y,n or all of above)
any clues,
thnx in advance
sudheer.
Regards,
Sudheer
-
If I understand, you are getting the criteria from a list box from a screen.
My suggestion is:
create procedure [dbo].[query] @ProgramType char(1), @AccountName char(1), @ProgramStatus char(1)
as
select ProgramId ProgramType AccountName ProgramName ProgramBudget ProgramStatus
from <table>
where ProgramType like @ProgramType
and AccountName like @AccountName
and ProgramStatus like @ProgramStatus
If in your list box you make the ALL selection have a value of % then it will act as a wildcard in the stored procedure.
Jeremy
-
I am assuming you are attempting to write a search criteria sort of query. I would recommend breaking into modular queries, with sets of stored procedures that handle each set of conditions. This way, the query plans will be stored that are optimized for each condition, not just the first set of parameters issued to a single procedure. Example:
CREATE PROC dbo.GetPrograms
@ProgramType INT = NULL
, @AccountName CHAR(1) = NULL
, @ProgramStatus BIT = NULL
AS
SET NOCOUNT ON
IF @ProgramType IS NULL BEGIN
IF @AccountName IS NULL BEGIN
IF @ProgramStatus IS NULL BEGIN
EXEC GetPrograms;2
END
ELSE BEGIN
EXEC GetPrograms;3 @ProgramStatus
END
END
ELSE BEGIN
IF @ProgramStatus IS NULL BEGIN
EXEC GetPrograms;5 @AccountName
END
ELSE BEGIN
EXEC GetPrograms;4 @AccountName , @ProgramStatus
END
-- Rest of switch logic here...
SET NOCOUNT OFF
GO
CREATE PROC dbo.GetPrograms;2
AS
SET NOCOUNT ON
SELECT ProgramId , ProgramType , AccountName ProgramName , ProgramBudget , ProgramStatus
FROM Program
SET NOCOUNT OFF
GO
CREATE PROC dbo.GetPrograms;3
@ProgramStatus BIT
AS
SET NOCOUNT ON
SELECT ProgramId , ProgramType , AccountName , ProgramName , ProgramBudget , ProgramStatus
FROM Program
WHERE ProgramStatus = @ProgramStatus
SET NOCOUNT OFF
GO
-- Rest of specialized procedures here
-- Forinstance, GetPrograms;4 , GetPrograms;5, etc.Advantages of this approach:
1) All procedures can be dropped in a single command (i.e. DROP PROC GetPrograms)
2) The code is modular, and changes in a single "subprocedure" will only force a recompile of that subprocedure alone.
3) All queries will have optimized execution plans, since query plan will be compiled for each subprocedure.
4) All switching logic is identified at the top of the procedure, making it easy to identify which subprocedure is handling which unique set of parameters.
5) Stored procedures can be called implicitly (relying on switching logic in main procedure, or explicitly. For example, calling EXEC GetPrograms @ProgramStatus=1 , @AccountName='A' will implicitly call GetPrograms;4, however, this subprocedure can also be called explicitly: EXEC GetPrograms;4 @AccountName='A' , ProgramStatus=1
6) No need for tens or hundreds of separate stored procedure names for essentially the same action...
This approach was taken from the idea of overloaded methods in common object-oriented programming techniques, and works well in many common scenarios. It can be a bit more code at first, but the approach is very modular, easy to maintain and modify, and the resulting SQL is usually simpler and more optimized than one giant complex statement.
Hope this gives you some ideas...
Jay
Edited by - jpipes on 05/20/2003 07:56:14 AM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply