|
Home > Archive > MS SQL Server New Users > May 2005 > StoredProc Help....
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
StoredProc Help....
|
|
| jamesfreddyc 2005-05-12, 11:23 am |
| Hi all,
I am trying to create a StoredProc to return an OUTPUT value. This output
is the difference between 2 datetime fields. I have had success with some
other OUTPUT StoredProcs, but were very straight forward computations -- this
date field stuff has me whipped....
Anyway, my main problem is that I do not understand how to make this OUTPUT
computation fit within an existing StoredProc, or perhaps how to create a new
one, yet perform the way I need it to.
I am making SELECT statments, first passing in 2 date parameters from my
application (an ArcGIS VBA app). Then making the correct table joins. And
finally returning a recordset to my app and doing the GIS stuff to the ADO
recordset. Pretty sure that you will understand the existing StoredProc, and
I would very much appreciate your input as to how to build-into this
procedure an OUTPUT of the difference bewtween "dbo.workitem.timestart" AND
"dbo.workitem.timeend"
Thanks a bunch!
james
****
CREATE PROCEDURE AO_GetWI_InspX
(@Insp char(15)=null,
@Date1 datetime=null
,
@Date2 datetime=null
,
@time datetime OUTPUT)
AS Set Nocount On
--return specified Ditch Indicator Sitest if Date and Status is supplied
if @Insp is not null
begin
SELECT
--retreive workitem data
dbo.workitem.serial,
dbo.workitem.timestart,
dbo.workitem.timeend,
dbo.workitem.task_type,
dbo.workitem.field_notes,
--retrieve employee data
dbo.employee.shortkey AS tech,
dbo.employee.phone_cell,
--retrieve location info
dbo.location.Loc_no,
dbo.location.zone,
dbo.location.city AS str,
dbo.location.hazards,
dbo.location.latitude,
dbo.location.longitude,
--location_role data
dbo.location_role.location_role,
--retrieve mosq_survey data
dbo.mosq_survey.dipstaken,
dbo.mosq_survey.landing_rate,
dbo.mosq_survey.num_posdips,
dbo.mosq_survey.landrate_samps,
dbo.mosq_survey.has_containers,
dbo.mosq_survey.workitem,
--retreive service_request data
dbo.service_request.record_date
FROM
dbo.workitem
INNER JOIN
dbo.employee ON dbo.workitem.employee = dbo.employee.serial
JOIN
dbo.location ON dbo.workitem.location = dbo.location.loc_no
INNER JOIN
dbo.mosq_survey ON dbo.workitem.serial = dbo.mosq_survey.workitem
INNER JOIN
dbo.location_role ON dbo.workitem.location = dbo.location_role.loc_no
INNER JOIN
dbo.service_request ON dbo.workitem.location =
dbo.service_request.location
WHERE
--dbo.workitem.task_type LIKE '%insp%' AND dbo.workitem.timestart >= @Date1
AND dbo.workitem.timeend <= @Date2
dbo.workitem.timestart >= @Date1 AND dbo.workitem.timeend <= @Date2
SELECT
--performance
--DateDiff(minute, DateOne, DateTwo)
@time = DateDiff(minute, dbo.workitem.timestart,dbo.workitem.timeend)
FROM
dbo.workitem
INNER JOIN
dbo.service_request ON dbo.workitem.location =
dbo.service_request.location
WHERE
dbo.workitem.timestart >= @Date1 AND dbo.workitem.timeend <= @Date2
Set Nocount Off
return 0
end
GO
| |
| Hugo Kornelis 2005-05-12, 8:23 pm |
| On Thu, 12 May 2005 08:01:06 -0700, jamesfreddyc wrote:
>Hi all,
>
>I am trying to create a StoredProc to return an OUTPUT value. This output
>is the difference between 2 datetime fields. I have had success with some
>other OUTPUT StoredProcs, but were very straight forward computations -- this
>date field stuff has me whipped....
>
>Anyway, my main problem is that I do not understand how to make this OUTPUT
>computation fit within an existing StoredProc, or perhaps how to create a new
>one, yet perform the way I need it to.
>
>I am making SELECT statments, first passing in 2 date parameters from my
>application (an ArcGIS VBA app). Then making the correct table joins. And
>finally returning a recordset to my app and doing the GIS stuff to the ADO
>recordset. Pretty sure that you will understand the existing StoredProc, and
>I would very much appreciate your input as to how to build-into this
>procedure an OUTPUT of the difference bewtween "dbo.workitem.timestart" AND
>"dbo.workitem.timeend"
>
>Thanks a bunch!
>
>james
Hi James,
Based on visual inspection, I'd say that your stored procedure is doing
just what you want it to. Of course, if the WHERE clause of the second
SELECT is satisfied by more than one row, only one of the computed time
differences will be returned to the calling application (and you have no
controle over which it will be), but that's a design issue, not a coding
issue. If your data is such that this query will always return exactly
one row, you should be fine.
Did you test your proc? What problems did you note?
If you need further assitance, then you should post more information:
tables (as CREATE TABLE statements, including constraints and
properties), some illustrative sample data (as INSERT statements),
expected output and the output you received. See www.aspfaq.com/5006 for
more details.
Groetjes, Hugo
--
(Hier had úw sig line kunnen staan)
| |
| jamesfreddyc 2005-05-13, 1:23 pm |
| Hugo -- Thanks for the reply!
Ok, here is a stepbystep of what I need to do...
1. Satisfy SELECT statement based upon the 2 INPUT parameters (@Date1,
@Date2).
2. Number 1 above makes a selection on the desired SQLServer Tables (this is
great and i can build my ADO recordset in the application from)
3. The additional thing I need to do is include an OUTPUT parameter to
determine the time difference between 2 date fields (in 2 different tables).
4. I am unsure of the second WHERE statement. That is, from the first WHERE
statement, I just want to "add" my OUTPUT calculation.
I have made some modifications to the original SProc and is error free.
However, when the app is run, the @time value is returned as "Empty". So, I
really have no idea where to go from here.
***StoredProc Upate***
CREATE PROCEDURE AO_GetWI_InspX
(@Insp char(15)=null,
@Date1 datetime=null
,
@Date2 datetime=null
,
@time int OUTPUT)
AS Set Nocount On
--return specified Ditch Indicator Sitest if Date and Status is supplied
--if @Insp is not null
begin
SELECT
--retreive workitem data
dbo.workitem.serial,
dbo.workitem.timestart,
dbo.workitem.timeend,
dbo.workitem.task_type,
dbo.workitem.field_notes,
--retrieve employee data
dbo.employee.shortkey AS tech,
dbo.employee.phone_cell,
--retrieve location info
dbo.location.Loc_no,
dbo.location.zone,
dbo.location.city AS str,
dbo.location.hazards,
dbo.location.latitude,
dbo.location.longitude,
--location_role data
dbo.location_role.location_role,
--retrieve mosq_survey data
dbo.mosq_survey.dipstaken,
dbo.mosq_survey.landing_rate,
dbo.mosq_survey.num_posdips,
dbo.mosq_survey.landrate_samps,
dbo.mosq_survey.has_containers,
dbo.mosq_survey.workitem,
--retreive service_request data
dbo.service_request.record_date
FROM
dbo.workitem
INNER JOIN
dbo.employee ON dbo.workitem.employee = dbo.employee.serial
JOIN
dbo.location ON dbo.workitem.location = dbo.location.loc_no
INNER JOIN
dbo.mosq_survey ON dbo.workitem.serial = dbo.mosq_survey.workitem
INNER JOIN
dbo.location_role ON dbo.workitem.location = dbo.location_role.loc_no
INNER JOIN
dbo.service_request ON dbo.workitem.location =
dbo.service_request.location
WHERE
dbo.workitem.timestart >= @Date1 AND dbo.workitem.timeend <= @Date2
SELECT
@time = DateDiff(hour, service_request.record_date, workitem.timeend)
FROM
dbo.workitem
INNER JOIN
dbo.service_request ON dbo.workitem.location =
dbo.service_request.location
Set Nocount Off
return @time
end
GO
****VB code below***
Public Function GetInspections() As ITable
'AO_GetSI_Sites
Dim pTable As ITable
Dim sInsp As String
Dim sDate1 As String
Dim sDate2 As String
sInsp = "inspect"
'If frmGeocode.chkSingleDt.Value = True Then
' sDate = frmGeocode.DTPicker1.Value
If frmGeocode.chkMultiDt.value = True Then
sDate1 = frmGeocode.DTPicker1.value & " " & "12:00:00 AM"
sDate2 = frmGeocode.DTPicker2.value & " " & "11:59:59 PM"
End If
Dim pConn As New ADODB.Connection
pConn = "Provider=SQLOLEDB.1; Data Source=MMSQL1;" & _
"Initial Catalog=vcms; User Id=mms; Password=mmspass"
pConn.Open
Dim pCommand As New ADODB.Command
pCommand.ActiveConnection = pConn
pCommand.CommandType = adCmdStoredProc
pCommand.CommandText = "AO_GetWI_InspX"
Dim objParam As New ADODB.Parameter
Dim objParam1 As New ADODB.Parameter
Set objParam1 = New ADODB.Parameter
Dim objParam2 As New ADODB.Parameter
Set objParam2 = New ADODB.Parameter
Dim timeParam As New ADODB.Parameter
Set timeParam = New ADODB.Parameter
objParam.Name = "Insp"
objParam1.Name = "Date1"
objParam2.Name = "Date2"
timeParam.Name = "time"
objParam.Type = adBSTR
objParam1.Type = adDate
objParam2.Type = adDate
timeParam.Type = adInteger
objParam.Direction = adParamInput
objParam1.Direction = adParamInput
objParam2.Direction = adParamInput
timeParam.Direction = adParamOutput
Dim time As Integer
objParam.value = sInsp
objParam1.value = sDate1
objParam2.value = sDate2
pCommand.Parameters.Append objParam
pCommand.Parameters.Append objParam1
pCommand.Parameters.Append objParam2
pCommand.Parameters.Append timeParam
'pCommand.Execute
Dim AdoCount As Integer
Dim AdoRecordset As ADODB.Recordset
Set AdoRecordset = New ADODB.Recordset
Set AdoRecordset.ActiveConnection = pConn
Set AdoRecordset = pCommand.Execute
AdoRecordset.Close
AdoRecordset.Open pCommand, , adOpenDynamic, adLockOptimistic
'set variables for reporting
time = timeParam.value
'Build field collection of ADO Recordset for temp table
Dim pADOFields As ADODB.Fields
Dim pADOField As ADODB.Field
Dim pFields As esriGeoDatabase.Fields
Dim pFieldsEdit As esriGeoDatabase.IFieldsEdit
Set pFields = New esriGeoDatabase.Fields
Set pFieldsEdit = pFields 'QI
Dim pField As esriGeoDatabase.IField
Dim pFieldEdit As esriGeoDatabase.IFieldEdit
Dim pDBFTable As ITable
Dim pRow As IRow
'add performance measure field to ADO Recorset
With AdoRecordset
.Close
'.ActiveConnection = Nothing
.Fields.Append "Performance", adDouble, 15
'.Open , pCommand, adOpenDynamic, adLockOptimistic
End With
Set pADOFields = AdoRecordset.Fields
For Each pADOField In pADOFields
Set pField = New esriGeoDatabase.Field
Set pFieldEdit = pField
With pFieldEdit 'set all the properties according to the columns in
the recordset
.Length = pADOField.DefinedSize
.Name = pADOField.Name
.Type = esriFieldTypeInteger
End With
If pADOField.Type = 131 Then
pFieldEdit.Type = esriFieldTypeDouble
ElseIf pADOField.Type = 129 Then
pFieldEdit.Type = esriFieldTypeString
ElseIf pADOField.Type = adDate Then
pFieldEdit.Type = esriFieldTypeDate
ElseIf pADOField.Type = 135 Then
pFieldEdit.Type = esriFieldTypeString
ElseIf pADOField.Type = 2 Then
pFieldEdit.Type = esriFieldTypeSmallIn
teger
ElseIf pADOField.Type = 20 Then
pFieldEdit.Type = esriFieldTypeSmallIn
teger
ElseIf pADOField.Type = 3 Then
pFieldEdit.Type = esriFieldTypeSmallIn
teger
ElseIf pADOField.Type = 200 Then
pFieldEdit.Type = esriFieldTypeString
ElseIf pADOField.Type = 201 Then
pFieldEdit.Type = esriFieldTypeString
End If
pFieldsEdit.AddField pField
Next
'Build .dbf table
Dim strFolder As String
Dim strTableName As String
Dim pFWS As IFeatureWorkspace
Dim pWorkspaceFactory As IWorkspaceFactory
Dim fs As Object
Dim varValue As Variant
Dim strValue As String
Dim dtValue As Date
Dim intI As Integer
Dim sTableName As String
Dim sFileDir As String
Dim sNewTableName As String
sFileDir = " H:\Shared\GIS\AO_VCM
S\db"
sNewTableName = " Inspections_TmpTable
"
Set pWorkspaceFactory = New ShapefileWorkspaceFa
ctory
Set fs = CreateObject("Scripting.FileSystemObject")
strFolder = " H:\Shared\GIS\AO_VCM
S\db"
If Not fs. FolderExists(strFold
er) Then
MsgBox "Folder does not exist: " & vbCr & strFolder
End
End If
'Get the output dataset name ready. The output dataset is a dbf file.
Dim pWkSpFactory As IWorkspaceFactory
Set pWkSpFactory = New ShapefileWorkspaceFa
ctory
Dim pWkSp As IWorkspace
Set pWkSp = pWkSpFactory. OpenFromFile(sFileDi
r, 0)
Dim pWkSpDS As IDataset
Set pWkSpDS = pWkSp
Dim pWkSpName As IWorkspaceName
Set pWkSpName = pWkSpDS.FullName
Dim pOutDSName As IDatasetName
Set pOutDSName = New tableName
pOutDSName.Name = sNewTableName
Set pOutDSName.WorkspaceName = pWkSpName
'Test to see if temp table already exists: if yes, delete it.
Dim pED As esriGeoDatabase.IEnumDataset
Set pED = pWkSp. Datasets(esriDTTable
)
Dim pDS As esriGeoDatabase.IDataset
Set pDS = pED.Next
Do Until pDS Is Nothing
Debug.Print pDS.Name
If pDS.Name = sNewTableName Then
pDS.Delete
Exit Do
End If
Set pDS = pED.Next
'Debug.Print pDS.Name
Loop
'Check to see if the dbf file already exists
If Dir$(sFileDir & "\" & sNewTableName & ".dbf") <> "" Then
Kill sFileDir & "\" & sNewTableName & ".dbf"
End If
Set pFWS = pWorkspaceFactory. OpenFromFile(strFold
er, 0)
Set pDBFTable = pFWS.CreateTable(" Inspections_TmpTable
", pFields, Nothing,
Nothing, "")
Dim pRowBuffer As IRowBuffer
Dim recCursor As ICursor
Set recCursor = pDBFTable.Insert(True)
'AdoRecordset.Close
AdoRecordset.Open pCommand, , adOpenDynamic, adLockOptimistic
With AdoRecordset
If .EOF = True Then
MsgBox "No Inspection Records For Specified Date Range -- Exiting",
vbExclamation
Exit Function
Else
' .MoveFirst
Do While Not .EOF
Set pRowBuffer = pDBFTable.CreateRowBuffer
'performance
If Not IsNull(AdoRecordset.Fields("performance").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("performanc")) =
AdoRecordset.Fields("performance").value
'workitem table data
If Not IsNull(AdoRecordset.Fields("serial").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("serial")) =
AdoRecordset.Fields("serial").value
If Not IsNull(AdoRecordset.Fields("task_type").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("task_type")) =
AdoRecordset.Fields("task_type").value
If Not IsNull(AdoRecordset.Fields("timestart").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("timestart")) =
AdoRecordset.Fields("timestart").value
If Not IsNull(AdoRecordset.Fields("timeend").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("timeend")) =
AdoRecordset.Fields("timeend").value
If Not IsNull(AdoRecordset.Fields("field_notes").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("field_note")) =
AdoRecordset.Fields("field_notes").value
'employee table data
If Not IsNull(AdoRecordset.Fields("tech").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("tech")) =
AdoRecordset.Fields("tech").value
If Not IsNull(AdoRecordset.Fields("phone_cell").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("phone_cell")) =
AdoRecordset.Fields("phone_cell").value
'location table data
If Not IsNull(AdoRecordset.Fields("loc_no").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("loc_no")) =
AdoRecordset.Fields("loc_no").value
If Not IsNull(AdoRecordset.Fields("str").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("str")) =
AdoRecordset.Fields("str").value
If Not IsNull(AdoRecordset.Fields("zone").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("zone")) =
AdoRecordset.Fields("zone").value
If Not IsNull(AdoRecordset.Fields("Latitude").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("Latitude")) =
AdoRecordset.Fields("Latitude").value
If Not IsNull(AdoRecordset.Fields("longitude").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("longitude")) =
AdoRecordset.Fields("longitude").value
If Not IsNull(AdoRecordset.Fields("hazards").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("hazards")) =
AdoRecordset.Fields("hazards").value
If Not IsNull(AdoRecordset.Fields("location_role").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("location_r")) =
AdoRecordset.Fields("location_role").value
'service_request table data
If Not IsNull(AdoRecordset.Fields("record_date").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("record_dat")) =
AdoRecordset.Fields("record_date").value
'If Not IsNull(AdoRecordset.Fields("location").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("dipstaken")) =
AdoRecordset.Fields("dipstaken").value
If Not IsNull(AdoRecordset.Fields("dipstaken").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("dipstaken")) =
AdoRecordset.Fields("dipstaken").value
If Not IsNull(AdoRecordset.Fields("num_posdips").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("num_posdip")) =
AdoRecordset.Fields("num_posdips").value
If Not IsNull(AdoRecordset.Fields("landing_rate").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("landing_ra")) =
AdoRecordset.Fields("landing_rate").value
If Not IsNull(AdoRecordset.Fields("has_containers").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("has_contai")) =
AdoRecordset.Fields("has_containers").value
If Not IsNull(AdoRecordset.Fields("landrate_samps").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("landrate_s")) =
AdoRecordset.Fields("landrate_samps").value
If Not IsNull(AdoRecordset.Fields("workitem").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("workitem")) =
AdoRecordset.Fields("workitem").value
recCursor.InsertRow pRowBuffer
.MoveNext
Loop
.Close
End If
End With
"Hugo Kornelis" wrote:
> On Thu, 12 May 2005 08:01:06 -0700, jamesfreddyc wrote:
>
>
> Hi James,
>
> Based on visual inspection, I'd say that your stored procedure is doing
> just what you want it to. Of course, if the WHERE clause of the second
> SELECT is satisfied by more than one row, only one of the computed time
> differences will be returned to the calling application (and you have no
> controle over which it will be), but that's a design issue, not a coding
> issue. If your data is such that this query will always return exactly
> one row, you should be fine.
>
> Did you test your proc? What problems did you note?
>
> If you need further assitance, then you should post more information:
> tables (as CREATE TABLE statements, including constraints and
> properties), some illustrative sample data (as INSERT statements),
> expected output and the output you received. See www.aspfaq.com/5006 for
> more details.
>
> Groetjes, Hugo
> --
>
> (Hier had úw sig line kunnen staan)
>
| |
| Hugo Kornelis 2005-05-16, 8:23 pm |
| On Fri, 13 May 2005 11:17:27 -0700, jamesfreddyc wrote:
>Hugo -- Thanks for the reply!
>
>Ok, here is a stepbystep of what I need to do...
>
>1. Satisfy SELECT statement based upon the 2 INPUT parameters (@Date1,
>@Date2).
>2. Number 1 above makes a selection on the desired SQLServer Tables (this is
>great and i can build my ADO recordset in the application from)
>3. The additional thing I need to do is include an OUTPUT parameter to
>determine the time difference between 2 date fields (in 2 different tables).
>4. I am unsure of the second WHERE statement. That is, from the first WHERE
>statement, I just want to "add" my OUTPUT calculation.
>
>I have made some modifications to the original SProc and is error free.
>However, when the app is run, the @time value is returned as "Empty". So, I
>really have no idea where to go from here.
(snip)
Hi James,
Well, the first thing to do is to find out why the time value is
returned as empty. It might be the stored proc, or it might be the VB
code. To check the stored proc without VB, open a Query Analyser window
and type (or copy) and execute the following:
DECLARE @Insp char(15)
, @Date1 datetime
, @Date2 datetime
, @time int
SET @Insp = '.....' <-- Supply your parameters here
SET @Date1 = 'yyyy-mm-ddThh:mm:ss' <-- Note the date/time format!
SET @Date2 = 'yyyy-mm-ddThh:mm:ss' <-- Use 24-hour clock, no AM/PM.
EXECUTE AO_GetWI_InspX
@Insp,
@Date1,
@Date2,
@time OUTPUT
SELECT @time
go
You should see the result set for the paramters you supplied, followed
by a disply of the value in @time after executing the procedure. If it's
listed as "(null)", there's something wrong in the stored proc. If it
shows the correct output, the proc is working all right, and the problem
is in how you call it from your VB code. If the latter is the case,
you'll need to find help form someone else, as I'm far from an expert in
that field.
Groetjes, Hugo
--
(Hier had úw sig line kunnen staan)
| |
| jamesfreddyc 2005-05-23, 1:23 pm |
| Hugo, thanks again for your input -- it's much appreciated...
It seems that I simply needed to treat the "OUTPUT" as another field in the
SELECT Statement. The DateDiff function works great, I just needed to
specify the field "AS" something.
This was a caluculation that needed to be performed on each subsequent
record in the recordset, then built into the attribute table of a shapefile
(an ESRI map layer).
Anyway -- below is how I handled everything, including the VB code. It's
working great and the client is incredibly pleased with the performance.
This app is dynamically building shapefiles from within ArcMap -- the
SQLServer2000 holds data being recorded by field technicians, truck mounted
GPS, as well as helicopter/airplane GPS data. This process of using
StoredProc's has cut the processing time by more than half of what it used to
be (building point locations from Latitude/Longitude coordinate pairs)...
Jamie,
StoredProc:
CREATE PROCEDURE AO_GetWI_InspX
(@Insp char(15)=null,
@Date1 datetime=null
,
@Date2 datetime=null
,
@time int OUTPUT)
AS Set Nocount On
begin
SELECT
--retreive workitem data
dbo.workitem.serial,
dbo.workitem.timestart,
dbo.workitem.timeend,
dbo.workitem.task_type,
dbo.workitem.field_notes,
--retrieve employee data
dbo.employee.shortkey AS tech,
dbo.employee.phone_cell,
--retrieve location info
dbo.location.Loc_no,
dbo.location.zone,
dbo.location.city AS str,
dbo.location.hazards,
dbo.location.latitude,
dbo.location.longitude,
--location_role data
dbo.location_role.location_role,
--retrieve mosq_survey data
dbo.mosq_survey.dipstaken,
dbo.mosq_survey.landing_rate,
dbo.mosq_survey.num_posdips,
dbo.mosq_survey.landrate_samps,
dbo.mosq_survey.has_containers,
dbo.mosq_survey.workitem,
dbo.mosq_survey.surveydate,
--retreive service_request data
dbo.service_request.record_date,
--performance
DateDiff(minute, service_request.record_date, workitem.timeend) AS Perf
FROM
dbo.workitem
LEFT JOIN
dbo.employee ON dbo.workitem.employee = dbo.employee.serial
LEFT JOIN
dbo.location ON dbo.workitem.location = dbo.location.loc_no
LEFT JOIN
--dbo.workitem ON dbo.mosq_survey.workitem = dbo.workitem.serial
dbo.mosq_survey ON dbo.workitem.serial = dbo.mosq_survey.workitem
LEFT JOIN
dbo.location_role ON dbo.workitem.location = dbo.location_role.loc_no
RIGHT JOIN
dbo.service_request ON dbo.workitem.location =
dbo.service_request.location
WHERE
dbo.workitem.timestart >= @Date1 AND dbo.workitem.timeend <= @Date2
AND dbo.workitem.serial = dbo.mosq_survey.workitem
***VB code for populating shapefile from ADO Recordset/stored proc:
Do While Not .EOF
Set pRowBuffer = pDBFTable.CreateRowBuffer
'performance
If Not IsNull(AdoRecordset.Fields("Perf").value) Then _
pRowBuffer.value(pRowBuffer.Fields.FindField("Perf")) =
AdoRecordset.Fields("Perf").value
"Hugo Kornelis" wrote:
> On Fri, 13 May 2005 11:17:27 -0700, jamesfreddyc wrote:
>
> (snip)
>
> Hi James,
>
> Well, the first thing to do is to find out why the time value is
> returned as empty. It might be the stored proc, or it might be the VB
> code. To check the stored proc without VB, open a Query Analyser window
> and type (or copy) and execute the following:
>
> DECLARE @Insp char(15)
> , @Date1 datetime
> , @Date2 datetime
> , @time int
> SET @Insp = '.....' <-- Supply your parameters here
> SET @Date1 = 'yyyy-mm-ddThh:mm:ss' <-- Note the date/time format!
> SET @Date2 = 'yyyy-mm-ddThh:mm:ss' <-- Use 24-hour clock, no AM/PM.
> EXECUTE AO_GetWI_InspX
> @Insp,
> @Date1,
> @Date2,
> @time OUTPUT
> SELECT @time
> go
>
> You should see the result set for the paramters you supplied, followed
> by a disply of the value in @time after executing the procedure. If it's
> listed as "(null)", there's something wrong in the stored proc. If it
> shows the correct output, the proc is working all right, and the problem
> is in how you call it from your VB code. If the latter is the case,
> you'll need to find help form someone else, as I'm far from an expert in
> that field.
>
> Groetjes, Hugo
> --
>
> (Hier had úw sig line kunnen staan)
>
| |
| Hugo Kornelis 2005-05-23, 8:24 pm |
| On Mon, 23 May 2005 10:17:27 -0700, jamesfreddyc wrote:
>Hugo, thanks again for your input -- it's much appreciated...
>
>It seems that I simply needed to treat the "OUTPUT" as another field in the
>SELECT Statement. The DateDiff function works great, I just needed to
>specify the field "AS" something.
>
>This was a caluculation that needed to be performed on each subsequent
>record in the recordset, then built into the attribute table of a shapefile
>(an ESRI map layer).
>
>Anyway -- below is how I handled everything, including the VB code. It's
>working great and the client is incredibly pleased with the performance.
(snip)
Hi Jamie,
And it might even be possible to improve it further. I'm not very good
at VB code, but after reviewing your post I have the feeling that the
calculated value (Perf) is the only result that you are interested in.
If that is correct, then you should remove all other columns from the
SELECT statement. Pushing all these values through the network when the
VB code will discard them anyway is just a waste of resources!
Removing all these columns from the output means that some of the tables
in the FROM clause can be removed as well. That should speed up the
execution time.
Finally, the RIGHT JOIN of workitem and service_request means that any
service_request without related workitem are retained, with NULL values
for the workitem columns - but since the workitem.timestart and
workitem.timeend are used in the WHERE clause, these retained rows will
be filtered out in the end result anyway. That makes the query
effectively equal to an INNER JOIN - and since an INNER JOIN can often
be executed with faster strategies, I've changed the join type as well.
The modified (untested) stored procedure:
CREATE PROCEDURE AO_GetWI_InspX
@Insp char(15)=null
,@Date1 datetime=nul
l
,@Date2 datetime=nul
l
-- parameter @time is not used anymore
-- ,@time int OUTPUT
AS
SET NOCOUNT ON
SELECT DATEDIFF(minute,
service_request.record_date,
workitem.timeend) AS Perf
FROM dbo.workitem
INNER JOIN dbo.service_request
ON dbo.workitem.location = dbo.service_request.location
WHERE dbo.workitem.timestart >= @Date1
AND dbo.workitem.timeend <= @Date2
Note - if I misread the VB code, and the columns are not unused, then
the above does not apply - except the part about the join type. You CAN
change the RIGHT JOIN to an INNER JOIN. You might also investigate if
all the LEFT JOINs can't be changed to INNER JOINs.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
|
|
|
|
|