'Created:	12/12/2002
'Author:	Marcus Tucker
'Function:	Executes a stored procedure (parameters are passed as two 1D arrays)
'Modified:	17/12/2002	Marcus Tucker	Improved parameter adding code + added some error checking
'			29/04/2003	Marcus Tucker	Parameter passing changed to two separate arrays (eliminates dictionary creation & destruction)
Function ExecuteSP(ByRef Conn, ByVal SPName, ByVal ParamNames, ByVal ParamValues)
	Dim Command, RecordSet
	Dim ParamCount
	Dim SPParamCount, Param, Count, Str
	Dim ParamName, ParamValue, SkipParams
	
	'Debug.Print "ExecuteSP() - Attempting to execute '" & SPName & "'"
	SkipParams = False
	
	'Set up the Command object
	Set Command = Server.CreateObject("ADODB.Command")
	Set Command.ActiveConnection = Conn
	Command.CommandType = adCmdStoredProc
	Command.CommandText = SPName
	
	'Populate the parameters collection
	Command.Parameters.Refresh
	
	Select Case True
		Case IsBlank(ParamNames) And IsBlank(ParamValues)
			SkipParams = True
			
		Case (IsArray(ParamNames) And IsArray(ParamValues))
			'both are arrays, that's perfect, do nothing!
		
		Case Not (IsArray(ParamNames) And IsArray(ParamValues))
			'neither are arrays, but they aren't blank,
			'so assume they are single values expressed as strings and convert to arrays
			ParamNames = Array(ParamNames)
			ParamValues = Array(ParamValues)
		
		Case Else
			'something else - throw an error
			Debug.Die "ExecuteSP() - " & SPName & " - " & Parameters 'ParamNames' and 'ParamValues' must either both be arrays or strings"
	End Select

	If Not SkipParams Then
		ParamCount = UBound(ParamNames) + 1
		SPParamCount = Command.Parameters.Count
	
		Select Case True
			'SP doesn't exist
			Case SPParamCount < 1
				Debug.Die "ExecuteSP() - " & SPName & " - Invalid stored procedure name """ & SPName & """"
			
			'Number of parameters don't match
			Case (SPParamCount - 1) <> ParamCount
				Str = "ExecuteSP() - " & SPName & " - Number of parameters do not match! Required parameters are:<br />"
				For Each Param in Command.Parameters
					Str = Str & " - " & Param.Name & "<br />"
				Next
				Debug.Die Str
			
			'Fine, carry on
			Case Else
				'Loop through and set all the parameters that were supplied
				For Count = 0 to (ParamCount - 1)
					ParamName = ParamNames(Count)
					ParamValue = ParamValues(Count)
					Set Param = Command.Parameters("@" & ParamName)
					If Param.Size < Len(ParamValue) Then
						'Die with an explanation
						Debug.Die "ExecuteSP() - " & SPName & " - Parameter '" & ParamName & "' has maximum field length of " & Param.Size & ", value '" & ParamValue & "' has length of " & Len(ParamValue)
					End If
					Param.Value = ParamValues(Count)
				Next
		End Select
	End If

	'Set up the RecordSet object
	Set RecordSet = Server.CreateObject("ADODB.RecordSet")
	RecordSet.CursorLocation = adUseClient
	RecordSet.CursorType = adOpenStatic
	RecordSet.LockType = adLockReadOnly
	
	'Execute the Query
	RecordSet.Open Command
	
	'Dispose of the Command object
	Set Command.ActiveConnection = Nothing
	Kill Command
	
	'Return the RecordSet
	Set ExecuteSP = RecordSet
End Function

quote:

---

Originally posted by AndrewMurphy

Remember if this code is execute 10000 times a day, then that's 10000 unnecessary calls to scan the SP for parameters.....and that performance hit all adds up.

---