MS SQL Stored Procedure from ASP

This is the preferred way to call a stored procedure from ASP. The number one reason to use this method is that it is nearly self-documenting. Without looking at the SQL Stored Procedure, the developer can tell what parameters are being sent, their data types, and their sizes. Also, this method can validate the data types before they go to the SQL Server. For example, if you passed a string into a parameter requiring an adInteger data type using this method, the ASP would fail before the command was executed, saving valuable resources. This method is also required if the stored procedure returns output parameters.

Dim objSQLConnection
objSQLConnection = Application("SQL_CONNECTION")  ' connection string stored in the application object

Dim objSQLCommand 	' Command object
Set objSQLCommand = Server.CreateObject("ADODB.Command")
dim objRS 		' Recordset object

With objSQLCommand
  .CommandText = "GetUserDetails_xsp"       ' Stored Proc Name
  .CommandType = adCmdStoredProc            ' Type of command
  .Parameters.Append .CreateParameter("@inUserName", adVarchar, adParamInput, 8, 'doejj')
  .Parameters.Append .CreateParameter("@inUWECID", adInteger, adParamInput, 8, 12345678)
  set objRS = .execute
end with
	
objRS.close
set objRS = nothing

set objSQLCommand = nothing