Vid anrop av en SP vill man ibland endast ha tillbaka ett eller flera värden. Jag önskar göra detta utan att skapa ett Recordset. Har någon en "smartare lösning" än denna?

Förutsättningen är att jag har en ADO.connection öppen.

I VB projektet har jag följande kod som sedan anropas enligt nedan.
<code>
Function fnRunSPStr(sProc As String, aParams As Variant) As String
Dim I As Integer, nParams As Integer

fnRunSPStr = ""

If ADO_CON.State = adStateOpen Then
Else
fnOpen_Connection
End If

On Error GoTo SQL_Error

ADO_CMD.ActiveConnection = ADO_CON
ADO_CMD.CommandType = adCmdStoredProc
ADO_CMD.CommandText = sProc

If Not IsEmpty(aParams) Then
nParams = UBound(aParams)
For I = 0 To nParams
ADO_CMD.Parameters(I + 1).Value = aParams(I)
' Debug.Print I & " " & aParams(I)
Next
End If

ADO_CMD.Execute

fnRunSPStr = ADO_CMD.Parameters(nParams + 1)

Set ADO_CMD.ActiveConnection = Nothing

Exit Function

SQL_Error:
MsgBox Str(Err.Number) + vbCrLf + Err.Description, , Err.Source
fnRunSPStr = "False"
End Function
</code>

I SQL servern finns exempelvis denna SP:

<code>
CREATE PROCEDURE lp_getFullNameFromLoginId
@sLoginId as nvarchar(10),
@sFullName as nvarchar(100) Output
AS
/*
Description: Returnerar Förnamn + Efternamn för specifikt LoginId

*/
SELECT @sFullName = 'Mr-X'

SELECT Top 1 @sFullName= (Fornamn + ' ' + Efternamn) FROM tblNamn
WHERE tblNamn.LoginID= ltrim(@sLoginId) AND
(tblNamn.Slutdatum > getdate()Or tblNamn.Slutdatum Is Null)

GO
</code>

VB funktionen anropas då enligt följande:
<code>
sFullName=fnRunSPStr("lp_getFullNameFromLoginId",Array("John","SOut"))
</code>

Min fråga är om någon har en mera flexibel lösning där man efter anropet av SP kan analysera om vilka output parametrar som fanns i SP'n.

John

Om du vill ha en smart lösning ur standard synpunkt är att ha specefika funktioner för varje procedur. Där du har deklarerat parametrarna:
<code>
Function GetFullName(LoginId As String) As String
If ADO_CON.State <> adStateOpen Then
fnOpen_Connection
End If
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = ADO_CON
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "lp_getFullNameFromLoginId"

cmd.Parameters.Append cmd.CreateParameter("sLoginId", adVarWChar, adParamInput, 10, LoginId)
cmd.Parameters.Append cmd.CreateParameter("sFullName", adVarWChar, adParamOutput, 100)

cmd.Execute , , adExecuteNoRecords

GetFullName = cmd.Parameters("sFullName")

End Function
</code>
Det är ju effektivast att göra på detta sätt.


Har försökt gör en generell funktion. Vilket anropar en procedur med ett antalt angivna argument:
<code>
Function RunStoredProc(Connection As ADODB.Connection, ProcName As String, ParamArray Parameters() As Variant) As Variant
Dim Index As Long
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = Connection
cmd.CommandText = ProcName
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
For Index = 0 To UBound(Parameters)
cmd.Parameters(Index + 1).Value = Parameters(Index)
Next
cmd.Execute
For Index = 0 To UBound(Parameters)
Parameters(Index) = cmd.Parameters(Index + 1).Value
Next
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing

End Function
</code>
Denna funktionen är ineffektiv eftersom den måste fråga servern vilka parametrara som finns. Vilket ger ett onödigt anrop.

Har skrivi en exempel för Northwind för att testa:
<code>
Private Sub Command1_Click()
Dim Result As Variant
Dim Con As ADODB.Connection
Set Con = New ADODB.Connection
Con.Open "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=localhost"
Result = Null
RunStoredProc Con, "spGetCustomerName", "ALFKI", Result
Debug.Print Result
Con.Close
End Sub
</code>
Den använder följande sp:
<code>
CREATE PROCEDURE spGetCustomerName
@CustomerID nchar(5),
@CompanyName nvarchar(40) OUTPUT
AS
SELECT @CompanyName = CompanyName
FROM dbo.Customers
WHERE CustomerID = @CustomerID
GO
</code>