Dynamic Stored Procedure

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

I am trying to build a report application for reporting off of SQL Server.
I have built my form for collecting the report criteria, and have built the
report. Now I am having problems interfacing my dynamic stored procedure
with the ADP.

Here is my Stored Procedure,

CREATE PROCEDURE spEventsByCatRegNoDate (@WHEREClause varchar(255))
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(1500)
-- Enter the dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = "SELECT UID, E.RegNo, ResEPI, CC.CliFName,
CC.CliLName, CC.CliMM, AggressorRegNo, AggResEPI, CA.CliFName AS AggFName,
CA.CliLName AS AggLName, CA.CliMM AS AggMM, Category, SubCategory,
EventDate, EventTime, Situation, EventBuilding,
Location, IllnessInjury, BodyPart, Mobility, Severity, Risk, MedAttnNeeded,
RiskMgrNotified, DRVDReport, BAR,
DeathReview, AddlInHouse, DGSDRMNotified, EmergencyCenter,
InfirmaryAdmission, RescueSquad, HospitializationRequired,
FallPrecaution, RepeatFaller, Litigations, Claims, HurtByAggressor,
EventClosed, PlanOfCorrection, Comments
FROM Events E INNER JOIN CliCore.dbo.tblClients CC ON E.RegNo = CC.RegNo
INNER JOIN
EventCat C ON E.EventCatID = C.CatID INNER JOIN EventSubCat SC ON
E.EventSubCatID = SC.SubCatID INNER JOIN
CliCore.dbo.tblClients CA ON E.AggressorRegNo = CA.RegNo INNER JOIN
Situation S ON E.EventSituationID = S.SituationID INNER JOIN
Location L ON E.EventLocationID = L.LocationID INNER JOIN
IllnessInjury II ON E.EventIllnessInjuryID = II.IllnessInjuryID INNER JOIN
BodyPart BP ON E.InjuredBodyPartID = BP.BodyPartID INNER JOIN
Mobility M ON E.EventMobilityID = M.MobilityID INNER JOIN
Severity SV ON E.EventSeverityID = SV.SeverityID INNER JOIN
Risk R ON E.EventRiskID = R.RiskID " + @WHEREClause
-- Execute the SQL statement
EXEC(@SQLStatement)
GO

The form has 7 form elements, Register #, Operator, Aggressor Register #,
Category, Subcategory, Start Date, End Date. I am trying to build this
report so that users only have to enter Register # and get a report, or they
can enter data into all of the form elements, or any combination of form
elements. I have built dynamic SQL statements in ASP before, and thought I
could bring that logic over to ADP's, but am currently having issues.

Here is the report's OnOpen event,

DoCmd.OpenForm "frmEventsByCatRegNoDate", acNormal, , , , acDialog

Dim strRecordSource As String
Dim RegNo As Integer
Dim Operator As String
Dim AggRegNo As Integer
Dim CatID As Integer
Dim SubCatID As Integer
Dim StartDate As Date
Dim EndDate As Date

RegNo = 0
If (Forms!frmEventsByCatRegNoDate.txtRegNo) <> "" Then
RegNo = Forms!frmEventsByCatRegNoDate.txtRegNo
End If

Operator = ""
If (Forms!frmEventsByCatRegNoDate.ddlOperator) <> "" Then
Operator = Forms!frmEventsByCatRegNoDate.ddlOperator
End If

AggRegNo = 0
If (Forms!frmEventsByCatRegNoDate.txtAggRegNo) <> "" Then
AggRegNo = Forms!frmEventsByCatRegNoDate.txtAggRegNo
End If

CatID = 0
If (Forms!frmEventsByCatRegNoDate.ddlCatID) <> "" Then
CatID = Forms!frmEventsByCatRegNoDate.ddlCatID
End If

SubCatID = 0
If (Forms!frmEventsByCatRegNoDate.ddlSubCatID) <> "" Then
SubCatID = Forms!frmEventsByCatRegNoDate.ddlSubCatID
End If

StartDate = 1 / 1 / 1900
If (Forms!frmEventsByCatRegNoDate.txtStartDate) <> "" Then
StartDate = Forms!frmEventsByCatRegNoDate.txtStartDate
End If

EndDate = 1 / 1 / 1900
If (Forms!frmEventsByCatRegNoDate.txtEndDate) <> "" Then
EndDate = Forms!frmEventsByCatRegNoDate.txtEndDate
End If

'For all records
strRecordSource = "Exec [spEventsByCatRegNoDate] " & "''"

If RegNo <> 0 Then
strRecordSource = "Exec [spEventsByCatRegNoDate] " & "'WHERE E.RegNo
= " & RegNo

If AggRegNo <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
If Operator <> "" Then
strRecordSource = strRecordSource & " " & Operator & "
E.AggressorRegNo = " & AggRegNo & "'"
Else
strRecordSource = strRecordSource & "E.AggressorRegNo = " &
AggRegNo
End If
End If
End If

If CatID <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = "'" & strRecordSource & "AND E.CatID = " &
CatID & "'"
Else
strRecordSource = strRecordSource & "E.CatID = " & CatID
End If
End If

If SubCatID <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = strRecordSource & "AND E.SubCatID = " &
SubCatID
Else
strRecordSource = strRecordSource & "E.SubCatID = " & SubCatID
End If
End If

If StartDate <> 1 / 1 / 1900 And EndDate <> 1 / 1 / 1900 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = strRecordSource & "AND EventDate BETWEEN '" &
StarDate & "' AND '" & EndDate & "'"
Else
strRecordSource = strRecordSource & "EventDate BETWEEN '" &
StarDate & "' AND '" & EndDate & "'"
End If
End If

End If
Me.RecordSource = strRecordSource
DoCmd.Close acForm, "frmEventsByCatRegNoDate"

Can anyone help me out with this issue? I just need some help building my
query so that it works.

Thanks,
Drew Laing
 
Hello Drew,
You wrote in conference microsoft.public.access.adp.sqlserver on Thu, 4 Aug
2005 16:49:48 -0400:

D> I am trying to build a report application for reporting off of SQL
D> Server. I have built my form for collecting the report criteria, and
D> have built the report. Now I am having problems interfacing my dynamic
D> stored procedure with the ADP.

D> Here is my Stored Procedure,



(follow hundred+ lines of complex-looking unformatted code without comments,
involving about a dozen tables of unknown structure and purpose; not a hint
anywhere about what actually does not work, other than "but am currently
having issues")



D> Can anyone help me out with this issue?

No Drew, sorry, nobody can help you :-(


Vadim
 
Use single quotes for @SQLStatement i.e.
SELECT @SQLStatement = 'SELECT UID, bah, blah, blah'
 
Vadim Rapp... so glad that you can spend the time to type out such a
helpful answer, a simple, "I'm not sure if you gave enough information"
or "In order to help you out, you will need to tell us what is wrong"
would have been sufficient.... So glad you could help!

Thanks for your post ambro, I would like to explain more of what my
issue is...

My issue is that, for example, if a user enters just the RegNo and
clicks submit everything works fine, but if the user enters a RegNo,
chooses an Operator (AND or OR), and enters an Aggressor RegNo then
clicks Get Report, the report errors out. If I capture the query with
Profiler, the query looks like this,

Exec [spEventsByCatRegNoDate] 'WHERE RegNo = 162'AND' AggRegNo = 162'

If the user enters only the Aggressor RegNo, then it works fine too.
The operator is what is throwing it off, but I'm not sure how to
overcome this issue.

Thanks!
Drew
 
d> Exec [spEventsByCatRegNoDate] 'WHERE RegNo = 162'AND' AggRegNo = 162'

d> If the user enters only the Aggressor RegNo, then it works fine too.
d> The operator is what is throwing it off, but I'm not sure how to
d> overcome this issue.


I didn't quite get this:

'WHERE RegNo = 162'AND' AggRegNo = 162'

if regno and addregno are integers, then the parameter you pass to the s.p.
should be

'WHERE REGNO=162 AND ADDREGNO=162'

if chars then

'WHERE REGNO=''162'' AND ADDREGNO=''162'''


Vadim
 
If you are using a Stored Procedure w/Parameters for a record source for a
form or report you can define the parameters in the InputParameters property
of the Form/Report. It is the last line of the data tab.

In here you can reference another forms controls. i.e. If I have Report1
that is based off of stored procedure "SP_Alpha" that requires a parameter
called @Key. The parameter needs to be defined as the KeyFieldValue on
FormA, which is in the text control "txtKey".

Assuming the parameter is and Integer, Report1's Data Source would be
"dbo.SP_Alpha"

In Report1's Input Parameter you would put something like @Key INT =
Forms("FormA")("txtKey")

This gives you dynamic control of a form or report based off of a
parameterized Stored porcedure.

HTH,
Jim

Drew said:
I am trying to build a report application for reporting off of SQL Server.
I have built my form for collecting the report criteria, and have built the
report. Now I am having problems interfacing my dynamic stored procedure
with the ADP.

Here is my Stored Procedure,

CREATE PROCEDURE spEventsByCatRegNoDate (@WHEREClause varchar(255))
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(1500)
-- Enter the dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = "SELECT UID, E.RegNo, ResEPI, CC.CliFName,
CC.CliLName, CC.CliMM, AggressorRegNo, AggResEPI, CA.CliFName AS AggFName,
CA.CliLName AS AggLName, CA.CliMM AS AggMM, Category, SubCategory,
EventDate, EventTime, Situation, EventBuilding,
Location, IllnessInjury, BodyPart, Mobility, Severity, Risk,
MedAttnNeeded, RiskMgrNotified, DRVDReport, BAR,
DeathReview, AddlInHouse, DGSDRMNotified, EmergencyCenter,
InfirmaryAdmission, RescueSquad, HospitializationRequired,
FallPrecaution, RepeatFaller, Litigations, Claims, HurtByAggressor,
EventClosed, PlanOfCorrection, Comments
FROM Events E INNER JOIN CliCore.dbo.tblClients CC ON E.RegNo = CC.RegNo
INNER JOIN
EventCat C ON E.EventCatID = C.CatID INNER JOIN EventSubCat SC ON
E.EventSubCatID = SC.SubCatID INNER JOIN
CliCore.dbo.tblClients CA ON E.AggressorRegNo = CA.RegNo INNER JOIN
Situation S ON E.EventSituationID = S.SituationID INNER JOIN
Location L ON E.EventLocationID = L.LocationID INNER JOIN
IllnessInjury II ON E.EventIllnessInjuryID = II.IllnessInjuryID INNER JOIN
BodyPart BP ON E.InjuredBodyPartID = BP.BodyPartID INNER JOIN
Mobility M ON E.EventMobilityID = M.MobilityID INNER JOIN
Severity SV ON E.EventSeverityID = SV.SeverityID INNER JOIN
Risk R ON E.EventRiskID = R.RiskID " + @WHEREClause
-- Execute the SQL statement
EXEC(@SQLStatement)
GO

The form has 7 form elements, Register #, Operator, Aggressor Register #,
Category, Subcategory, Start Date, End Date. I am trying to build this
report so that users only have to enter Register # and get a report, or
they can enter data into all of the form elements, or any combination of
form elements. I have built dynamic SQL statements in ASP before, and
thought I could bring that logic over to ADP's, but am currently having
issues.

Here is the report's OnOpen event,

DoCmd.OpenForm "frmEventsByCatRegNoDate", acNormal, , , , acDialog

Dim strRecordSource As String
Dim RegNo As Integer
Dim Operator As String
Dim AggRegNo As Integer
Dim CatID As Integer
Dim SubCatID As Integer
Dim StartDate As Date
Dim EndDate As Date

RegNo = 0
If (Forms!frmEventsByCatRegNoDate.txtRegNo) <> "" Then
RegNo = Forms!frmEventsByCatRegNoDate.txtRegNo
End If

Operator = ""
If (Forms!frmEventsByCatRegNoDate.ddlOperator) <> "" Then
Operator = Forms!frmEventsByCatRegNoDate.ddlOperator
End If

AggRegNo = 0
If (Forms!frmEventsByCatRegNoDate.txtAggRegNo) <> "" Then
AggRegNo = Forms!frmEventsByCatRegNoDate.txtAggRegNo
End If

CatID = 0
If (Forms!frmEventsByCatRegNoDate.ddlCatID) <> "" Then
CatID = Forms!frmEventsByCatRegNoDate.ddlCatID
End If

SubCatID = 0
If (Forms!frmEventsByCatRegNoDate.ddlSubCatID) <> "" Then
SubCatID = Forms!frmEventsByCatRegNoDate.ddlSubCatID
End If

StartDate = 1 / 1 / 1900
If (Forms!frmEventsByCatRegNoDate.txtStartDate) <> "" Then
StartDate = Forms!frmEventsByCatRegNoDate.txtStartDate
End If

EndDate = 1 / 1 / 1900
If (Forms!frmEventsByCatRegNoDate.txtEndDate) <> "" Then
EndDate = Forms!frmEventsByCatRegNoDate.txtEndDate
End If

'For all records
strRecordSource = "Exec [spEventsByCatRegNoDate] " & "''"

If RegNo <> 0 Then
strRecordSource = "Exec [spEventsByCatRegNoDate] " & "'WHERE
E.RegNo = " & RegNo

If AggRegNo <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
If Operator <> "" Then
strRecordSource = strRecordSource & " " & Operator & "
E.AggressorRegNo = " & AggRegNo & "'"
Else
strRecordSource = strRecordSource & "E.AggressorRegNo = " &
AggRegNo
End If
End If
End If

If CatID <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = "'" & strRecordSource & "AND E.CatID = " &
CatID & "'"
Else
strRecordSource = strRecordSource & "E.CatID = " & CatID
End If
End If

If SubCatID <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = strRecordSource & "AND E.SubCatID = " &
SubCatID
Else
strRecordSource = strRecordSource & "E.SubCatID = " & SubCatID
End If
End If

If StartDate <> 1 / 1 / 1900 And EndDate <> 1 / 1 / 1900 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = strRecordSource & "AND EventDate BETWEEN '" &
StarDate & "' AND '" & EndDate & "'"
Else
strRecordSource = strRecordSource & "EventDate BETWEEN '" &
StarDate & "' AND '" & EndDate & "'"
End If
End If

End If
Me.RecordSource = strRecordSource
DoCmd.Close acForm, "frmEventsByCatRegNoDate"

Can anyone help me out with this issue? I just need some help building my
query so that it works.

Thanks,
Drew Laing
Click to expand...
 
Oops....Hit reply on the wrong thread

Sorry,
JIm

J. Clay said:
If you are using a Stored Procedure w/Parameters for a record source for a
form or report you can define the parameters in the InputParameters
property
of the Form/Report. It is the last line of the data tab.

In here you can reference another forms controls. i.e. If I have Report1
that is based off of stored procedure "SP_Alpha" that requires a parameter
called @Key. The parameter needs to be defined as the KeyFieldValue on
FormA, which is in the text control "txtKey".

Assuming the parameter is and Integer, Report1's Data Source would be
"dbo.SP_Alpha"

In Report1's Input Parameter you would put something like @Key INT =
Forms("FormA")("txtKey")

This gives you dynamic control of a form or report based off of a
parameterized Stored porcedure.

HTH,
Jim

Drew said:
I am trying to build a report application for reporting off of SQL Server.
I have built my form for collecting the report criteria, and have built
the report. Now I am having problems interfacing my dynamic stored
procedure with the ADP.

Here is my Stored Procedure,

CREATE PROCEDURE spEventsByCatRegNoDate (@WHEREClause varchar(255))
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(1500)
-- Enter the dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = "SELECT UID, E.RegNo, ResEPI, CC.CliFName,
CC.CliLName, CC.CliMM, AggressorRegNo, AggResEPI, CA.CliFName AS
AggFName, CA.CliLName AS AggLName, CA.CliMM AS AggMM, Category,
SubCategory, EventDate, EventTime, Situation, EventBuilding,
Location, IllnessInjury, BodyPart, Mobility, Severity, Risk,
MedAttnNeeded, RiskMgrNotified, DRVDReport, BAR,
DeathReview, AddlInHouse, DGSDRMNotified, EmergencyCenter,
InfirmaryAdmission, RescueSquad, HospitializationRequired,
FallPrecaution, RepeatFaller, Litigations, Claims, HurtByAggressor,
EventClosed, PlanOfCorrection, Comments
FROM Events E INNER JOIN CliCore.dbo.tblClients CC ON E.RegNo = CC.RegNo
INNER JOIN
EventCat C ON E.EventCatID = C.CatID INNER JOIN EventSubCat SC ON
E.EventSubCatID = SC.SubCatID INNER JOIN
CliCore.dbo.tblClients CA ON E.AggressorRegNo = CA.RegNo INNER JOIN
Situation S ON E.EventSituationID = S.SituationID INNER JOIN
Location L ON E.EventLocationID = L.LocationID INNER JOIN
IllnessInjury II ON E.EventIllnessInjuryID = II.IllnessInjuryID INNER
JOIN
BodyPart BP ON E.InjuredBodyPartID = BP.BodyPartID INNER JOIN
Mobility M ON E.EventMobilityID = M.MobilityID INNER JOIN
Severity SV ON E.EventSeverityID = SV.SeverityID INNER JOIN
Risk R ON E.EventRiskID = R.RiskID " + @WHEREClause
-- Execute the SQL statement
EXEC(@SQLStatement)
GO

The form has 7 form elements, Register #, Operator, Aggressor Register #,
Category, Subcategory, Start Date, End Date. I am trying to build this
report so that users only have to enter Register # and get a report, or
they can enter data into all of the form elements, or any combination of
form elements. I have built dynamic SQL statements in ASP before, and
thought I could bring that logic over to ADP's, but am currently having
issues.

Here is the report's OnOpen event,

DoCmd.OpenForm "frmEventsByCatRegNoDate", acNormal, , , , acDialog

Dim strRecordSource As String
Dim RegNo As Integer
Dim Operator As String
Dim AggRegNo As Integer
Dim CatID As Integer
Dim SubCatID As Integer
Dim StartDate As Date
Dim EndDate As Date

RegNo = 0
If (Forms!frmEventsByCatRegNoDate.txtRegNo) <> "" Then
RegNo = Forms!frmEventsByCatRegNoDate.txtRegNo
End If

Operator = ""
If (Forms!frmEventsByCatRegNoDate.ddlOperator) <> "" Then
Operator = Forms!frmEventsByCatRegNoDate.ddlOperator
End If

AggRegNo = 0
If (Forms!frmEventsByCatRegNoDate.txtAggRegNo) <> "" Then
AggRegNo = Forms!frmEventsByCatRegNoDate.txtAggRegNo
End If

CatID = 0
If (Forms!frmEventsByCatRegNoDate.ddlCatID) <> "" Then
CatID = Forms!frmEventsByCatRegNoDate.ddlCatID
End If

SubCatID = 0
If (Forms!frmEventsByCatRegNoDate.ddlSubCatID) <> "" Then
SubCatID = Forms!frmEventsByCatRegNoDate.ddlSubCatID
End If

StartDate = 1 / 1 / 1900
If (Forms!frmEventsByCatRegNoDate.txtStartDate) <> "" Then
StartDate = Forms!frmEventsByCatRegNoDate.txtStartDate
End If

EndDate = 1 / 1 / 1900
If (Forms!frmEventsByCatRegNoDate.txtEndDate) <> "" Then
EndDate = Forms!frmEventsByCatRegNoDate.txtEndDate
End If

'For all records
strRecordSource = "Exec [spEventsByCatRegNoDate] " & "''"

If RegNo <> 0 Then
strRecordSource = "Exec [spEventsByCatRegNoDate] " & "'WHERE
E.RegNo = " & RegNo

If AggRegNo <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
If Operator <> "" Then
strRecordSource = strRecordSource & " " & Operator & "
E.AggressorRegNo = " & AggRegNo & "'"
Else
strRecordSource = strRecordSource & "E.AggressorRegNo = "
& AggRegNo
End If
End If
End If

If CatID <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = "'" & strRecordSource & "AND E.CatID = " &
CatID & "'"
Else
strRecordSource = strRecordSource & "E.CatID = " & CatID
End If
End If

If SubCatID <> 0 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = strRecordSource & "AND E.SubCatID = " &
SubCatID
Else
strRecordSource = strRecordSource & "E.SubCatID = " & SubCatID
End If
End If

If StartDate <> 1 / 1 / 1900 And EndDate <> 1 / 1 / 1900 Then
If strRecordSource <> "Exec [spEventsByCatRegNoDate] " & "''" Then
strRecordSource = strRecordSource & "AND EventDate BETWEEN '"
& StarDate & "' AND '" & EndDate & "'"
Else
strRecordSource = strRecordSource & "EventDate BETWEEN '" &
StarDate & "' AND '" & EndDate & "'"
End If
End If

End If
Me.RecordSource = strRecordSource
DoCmd.Close acForm, "frmEventsByCatRegNoDate"

Can anyone help me out with this issue? I just need some help building
my query so that it works.

Thanks,
Drew Laing
Click to expand...
Click to expand...
 
Back
Top