Hello all.

I am pretty new to SQL Server, and I am using SQL Server 2005 Developers
edition.
I am sorry if this question has a really obvious answer, but I am just
starting to use the SQL Server 2005.

I wonder whether you could answer this question for me.

In my large VB6 application,  that I want to start to transfer to a SQL
Server 2005 database, I query an Access 2000 database to find  laboratory
instruments, (MethodID), used in an analysis in a survey.

First I get a list of different instruments used in the survey, then I get
the stats for each individual instrument group. In essence, I get a Average
result for each instrument group.

In my function, it is simple, I get a list of instrument, put them into a
recordset, and then use the recordset to advance throught the instrument
groups  to get the individual instrument Average result:

eg:

In a function
Public Function GetAverages(Survey as Integer)

Open the connection etc....
........................
....................
Dim rsGeneric as New ADODB.recordset
Dim rsAverage as New ADODB.recordset

Dim strSQl as String
Dim Method as Integer
Dim Average as Single

strSQL = "SELECT APTTResults.MethodID " _
             & "FROM APTTResults " _
             & "WHERE (((APTTResults.Survey)= "  & Survey &")) " _
'*Survey is passed into the function
             & "GROUP BY APTTResults.MethodID"

   Set rsGeneric = cnMyDatabase.Execute(strSQL, , adCmdText)
   Set rsGeneric.ActiveConnection = Nothing
   strSQL = ""

If rsgeneric.RecordCount >0 then
   rsGeneric.MoveFirst

        Do While Not rsGeneric.EOF

       Method =0
       Method = rsGeneric!MethodID

'Get the  Average etc

strSQL = "SELECT Avg([Ratio]) AS Average " _
           & "FROM APTTResults " _
           & "WHERE (((APTTResults.MethodID)= " & Method  &") " _
'**Take the MethodID from the rsGeneric Recordset
           & "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)

........................
.....Write the MethodID and Average to a Temp table

rsgeneric.Movenext
Loop
.....................
..............

End Function

My question is, how do I do this in a stored Procedure, ie, how do I pass
the MethodID  from the first rsGeneric Recordset as a parameter to be used
in the second SQL statement?   I haven't seen recordsets being used in
stored procedures either. My next question relates to writing this back to a
temporary table within the stored procedure, but that can wait for another
session.

My calculation as listed above are quite complex and long, but I first need
to understand how this is accomplished in a stored procedure. Is it the
same, except using T-SQL?  My book doesn't really show any examples of how
to use parameters from a recordset that has been derived from within a
stored procedure. I would imagine that it is the same, but I just want to
check.


Thank you for any help that you can offer.

Best regards.

David Clifford

How to concatenate strings from a column into a single row (with NULL value)?
Single to Multiple columns
Assistance with SQL Script
SQL 2005: Why does DECLARE varchar(MAX) in Stored Procedure not work?
HOW TO: PIVOT for unknown number of column headings?