Help Writing Stored Procedure With Parameters

[jtrapat1]

I'm using CR8 against an SQL Server 7 database.

I'm trying to write a stored procedure that takes two parameters to determine the type of report to generate.
There are four different reports and the only thing that is different is the WHERE clause.

Here's the four different WHERE clauses from the Record Selection Criteria from Crystal Reports:

if {?ReportType} = "1" then
{Bill.Owner} = {?Owner}
else

if {?ReportType} = "2" then
{Privilege.Supervisor} = {?Deputy}
else

if {?ReportType} = "3" then
{BillAssignments.Analyst} = {?Analyst}
else

if {?ReportType} = "4" then
(generate full report)

I would like to write the most efficient sp here but I don't know if it should have a CASE structure or IF, THEN, ELSE.

Can anyone give me a direction to pursue here?

Thanks in Advance.

John

 

[tlbroadbent]

You can use IF... ELSE statements to determine which query to run as in the following example.

Create Procedure usp_SelectRecsForRpt @rpttype int, @rptuser varchar(40)=Null As

If @rpttype=1
Begin
Select Col1, Col2, Col3, ....
From table1 Where Owner=@rptuser
End
Else

If @rpttype=2
Begin
Select Col1, Col2, Col3, ....
From table1 Where Supervisor=@rptuser
End
Else

If @rpttype=3
Begin
Select Col1, Col2, Col3, ....
From table1 Where Analyst=@rptuser
End
Else

If @rpttype=4
Begin
Select Col1, Col2, Col3, ....
From table1
End
Go Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[jtrapat1]

Terry,

Thanks for the help.

That was the exact problem.
I just did not know how to structure the code.

I thought I had to get into PL/SQL, CASE statements, and worry about cursors and stuff like that but you made it easy.

Thanks again for the help.

John

 

[jtrapat1]

Terry,

Thanks for the help.

That was the exact problem.
I just did not know how to structure the code.

I thought I had to get into PL/SQL, CASE statements, and worry about cursors and stuff like that but you made it easy.

Also, I wasn't sure about testing for NULL values.

Thanks again for the help.

John