How do you use stored procs to filter data?

[MPBCHM]

I need to create over 40 different reports that pulls data from the same 4 tables. What I have done so far is to create a view for each report that filters the data accordingly. This has left me with 40 Views (SQLSERVER) and 40 reports(Crystal). I know theres an easier way to do this and so far Stored Procedures seems to be the way to go.

What I want is a Stored Procedure(s) that filters the data for each report. If I could run all forty reports from one stored procedure instead of 40 views and 40 reports, mylife would be much easier.

Here is an example of a stored proc I have been working on. I just need a way to consolidate all the seperate SP I would need to run all 40 reports.

----- Stored Procedure 1 ------

CREATE PROCEDURE [AFLU]
AS
Declare @ReportType Varchar(8)
Set @ReportType = 'AFLU'

Select
dbo.claim_details.claimno,
dbo.claim_details.diagcode,
LTRIM(dbo.claim_details.proccode) as 'PROCCODE',
@ReportType as 'TYPE'

FROM
dbo.claim_details
Where
LTRIM(dbo.claim_details.proccode) = '90724'

------ Stored Procdure 2 --------

CREATE PROCEDURE [ESRD]
AS
Declare @ReportType Varchar(8)
Set @ReportType = 'ESRD'

Select
dbo.claim_details.claimno,
dbo.claim_details.diagcode,
LTRIM(dbo.claim_details.proccode) as 'PROCCODE',
@ReportType as 'TYPE'

FROM
dbo.claim_details
Where
LTRIM(dbo.claim_details.proccode) = '90443'and
LTRIM(dbo.claim_details.diagcode) = 'V24.5'




RESULTS:

claimno diagcode PROCCODE TYPE
-------------------- -------- --------------- --------
1999081302400007 719.46 90724 AFLU
1999082302400093 780.4 90724 AFLU
1999083100400177 692. 90724 AFLU
1999090100400028 V06.9 90724 AFLU
1999101102400033 493.9 90724 AFLU
1999151107100087 V24.5 90443 ESRD
2000140805200001 V24.5 90443 ESRD
2000150804200089 V24.5 90443 ESRD



Now, if I could insert this data into a temp table along with the other 40 , I could run all my reports from one SP
using my report writer to sort and group the data for each report.

Thanks

Mark

 

[TomSark]

Why don't you simply create one proc that builds the sql statement you are executing into a varchar variable, then use the EXECUTE statement to run it? You would dynamically build the where clause based on the parameters you pass into the proc.

Ex:

CREATE PROCEDURE new_proc (@ReportType Varchar(8))
AS

DECLARE @SQLStr varchar(2000)

IF @ReportType = 'AFLU'
BEGIN
SET @SQLStr = ' Select
dbo.claim_details.claimno,
dbo.claim_details.diagcode,
LTRIM(dbo.claim_details.proccode) as [PROCCODE],'
+ @ReportType + ' as [TYPE]
FROM
dbo.claim_details
Where
LTRIM(dbo.claim_details.proccode) = ' + '90724'
ELSE IF @REportType = 'YADDA YADDA YADDA'
-- build sql str for this report and so on
END

EXECUTE (@SQLStr)
RETURN 0

This way you have one proc, that's it. You simply code the various needed select statement strings and execute them. The only difference is that you call this proc with the appropriate report type parameter to get the output you want.

Lemme know if this helps... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[MPBCHM]

Thanks for the quick reply...

What I want to do is Create a table, insert data, and based on filter criteria, tag each record line with code...

Code Name Filter:
AFLU Adult Flu Report claim_details.proccode = '90724'
DENT Dental Report claim_details.proccode = 'D1310'
ERLO ER Log claim_details.proccode = '99281'

so on & so on...

My first example was my Beginner approach to this problem.

Now, with all the data in one table, I can use my report writer (Crystal) to sort/group records by @Report_Type field then print the whole thing at one time.

Basically, I want to do all the filtering in sql and use my report writer to organize, label, and count the results.

Is there a better way to do this?

What I have now is 30 different reports to run each month. I would like to trim that down to one.

Any ideas would be greatly appreciated.

Thanks again

Mark