February 27, 2008 at 10:06 am
Can any one help me?
I created a procedure that will select status of an item:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetReport]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create PROCEDURE dbo.sp_GetReport
@Clause as varchar(30),
@StartDate as nvarchar(30),
@EndDate as nvarchar(30)
AS
Begin
SELECT COLUMN
FROM TABLE
WHERE
COLUMNSTATUS in (@clause) and COLUMNDATE between @StartDate and @EndDate
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The column status has many status defined but the program will only give three status which is open, closed, and deferred which the program will only pass a coma delimiter for the status(open,closed,deferred ) the status might be only one, two or all of them
But the problem is it should be dynamic even I will add new status for open items there should be no changes on the SP…
COLUMNStatus:
S1
S2
S3
S4
closed
deferred
So from s1 to s4 those status are considered open, so how can I declare those status as a single variable as open?
February 27, 2008 at 1:11 pm
The way I would do it is to create a 'StatusTable' that contains the StatusID (S1, S2, etc), and a disposition for the status (open, closed, deferred).
It might look like this...
StatusLookupTable
StatusID Disposition
S1 open
closed closed
S2 open
S3 open
deferred deferred
If you already have a similar table...you can use it and add the 'Disposition' column or whatever you want to call it. 🙂
This table will allow you to add, delete, and edit statuses and their disposition easily without having to edit the stored procedure everytime you need a change.
Going back to the original stored procedure, include the StatusLookupTable in your select statement.
SELECT COLUMN
FROM TABLE inner join StatusLookupTable
on columnstatus = statusid
WHERE DISPOSITION in (@clause).
AND COLUMNDATE between @StartDate and @EndDate
I think I gave you enough details to make this work.
I hope this helps!
If it was easy, everybody would be doing it!;)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply