asterisks in form parameters

C

Candace

I'm trying to filter a report using form parameters and a
stored procedure. It works great until I add a form
parameter that may use an asterisk if the user wants to
return all values.

Any thoughts?

Candace

Here's the stored procedure below. The variables
Nursing_Unit, Team or Program may have an asterisk at any
given time.

Alter PROCEDURE tmp @Start_Date datetime, @End_Date
datetime, @Nursing_Unit nvarchar(25), @Team nvarchar(50),
@Program nvarchar(50), @Type_of_Vein nvarchar(20)

AS SELECT dbo.Patient.Patient_ID,
dbo.Patient.Patient_Surname, dbo.Device.Device_CVAD_Type,
dbo.Device.Device_Vein_Line,
dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit_PC,
dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit,
dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping,
dbo.Patient_Care_Area.Patient_Care_Area_Program,
dbo.Infection.Infection_ID, dbo.Infection.Infection_Type,
dbo.Infection.Infection_Nosocomial,dbo.Infection.Infection_
Polymicrobic, dbo.Infection.Infection_First_Organism_ID,
dbo.Infection.Infection_Second_Organism_ID,
dbo.Infection.Infection_Third_Organism_ID,
dbo.Infection.Infection_Diagnosis,
dbo.Organism.Organism_Name1,
dbo.Day.Day_CVAD_Date

FROM dbo.Patient RIGHT OUTER JOIN dbo.Patient_Care_Area
RIGHT OUTER JOIN dbo.Device LEFT OUTER JOIN dbo.Day ON
dbo.Device.Device_New_CVAD_ID
= dbo.Day.Day_New_CVAD_ID ON
dbo.Patient_Care_Area.Patient_Care_Area_ID =
dbo.Day.Day_Patient_Care_Area_ID ON dbo.Patient.Patient_ID
=
dbo.Device.Device_Patient_ID LEFT OUTER JOIN dbo.Organism
RIGHT OUTER JOIN dbo.Infection ON dbo.Organism.Organism_ID
= dbo.Infection.Infection_First_Organism_ID
ON dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID

WHERE
(dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit LIKE
@Nursing_Unit) and
(dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
LIKE @Team) and
(dbo.Patient_Care_Area.Patient_Care_Area_Program LIKE
@Program) and (dbo.Device.Device_Vein_Line LIKE
@Type_of_Vein) AND (dbo.Infection.Infection_Nosocomial
= 'Yes')

GROUP BY dbo.Patient.Patient_ID,
dbo.Patient.Patient_Surname, dbo.Device.Device_CVAD_Type,
dbo.Device.Device_Vein_Line,
dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit_PC,
dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit,
dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping,
dbo.Patient_Care_Area.Patient_Care_Area_Program,
dbo.Infection.Infection_ID, dbo.Infection.Infection_Type,
dbo.Infection.Infection_Nosocomial,
dbo.Infection.Infection_Polymicrobic,
dbo.Infection.Infection_First_Organism_ID,
dbo.Infection.Infection_Second_Organism_ID,
dbo.Infection.Infection_Third_Organism_ID,
dbo.Infection.Infection_Diagnosis,
dbo.Organism.Organism_Name1,
dbo.Day.Day_CVAD_Date

HAVING ((dbo.Infection.Infection_Type = 'Blood') OR
(dbo.Infection.Infection_Type = 'Site') OR
(dbo.Infection.Infection_Type = 'Both')) And
(dbo.Day.Day_CVAD_Date between @Start_Date and @End_Date)
 
Top