sql server programming:
Hello all
I am pretty new to SQL Server 2005, and so I am sorry if this question has
an obvious answer.
I have a large VB6/Jet 4.0 application that I want to migrate to VB6/SQL
Server 2005.
In my VB application, I have some quite complex calculations that take
instrument groups, get results from an individual survey, and Average out
the resutls for each individual instrument group used within the survey.
eg:
This is a simple example of what I currently do in a function, but now I
want to put all of this into a Stored Procedure:
I am only interested in the recordset bit, not how to call the Stored
Procedure in the function..I managed to figure that bit out myself.
In a function...
Public Function GetAverage(Survey as Integer)
'........Open the database connection etc
.................................
.............
Dim rsGeneric as ADODB.Recordset
Dim rsAverage as ADODB.Recordset
Dim Method as Integer
Dim Average as Single
Dim strSQL as String
'*First get the Instrument Groups
strSQL = "SELECT APTTResults.MethodID " _
& "FROM APTTResults " _
& "WHERE (((APTTResults.Survey)= " & Survey & ") " _
& "AND ((APTTResults.Ratio)>-1)) " _
& "GROUP BY APTTResults.MethodID"
Set rsGeneric = cnMyDatabase.Execute(strSQL, , adCmdText)
'*This is where my question really starts from
Set rsGeneric.ActiveConnection = Nothing
strSQL = ""
If rsGeneric.Recordcount > 0 then
rsGeneric.MoveFirst
Do While Not rsGeneric.EOF
Method = 0
Method = rsGeneric!MethodID
'*Now get the Average result for each instriument
strSQL = "SELECT Avg([Ratio]) AS Average " _
& "FROM APTTResults " _
& "WHERE (((APTTResults.MethodID)= " & Method & ") " _
'*How do I pass this parameter, (Method), in SQL Server 2005?
& "AND ((APTTResults.Survey)= " & Survey &") " _
& "AND ((APTTResults.Ratio)>-1))"
Set rsAverage = cnMyDatabase.Execute(strSQL, , adCmdText)
Set rsAverage .ActiveConnection = Nothing
strSQL = ""
Average = 0
Average = rsAverage.Fields(0)
Set rsAverage =Nothing
.....Write this to a Stats Table
.......................
............................
rsGeneric.Movenext
Loop
Set rsGeneric=Nothing
End If
End Function
This is all pretty simple, and it all works. However, in my SQL Server book,
I can't find an example of how to use a parameter that has been derived from
a primary recordset to supply a further parameter to another query statement
within the Stored Procedure. In fact, I don't see any use of recordsets
within Stored Procedures at all. Does one use recordsets within Stored
Procedures? Is the syntax the same, as the above, except using T-SQL?
Any help or references would be most gratefully received.
I'm sorry if this is a really daft question, but for me, it is still very
early days.
Beast regards
David Clifford
