ASP is a program based on Microsoft Technologies that runs inside a web server.

Below is the method used for calling a Stored Procedure in MSSQLSERVER using classic ASP scripting.

The variable SystemDSN holds the value of the Data Source Name that is configured using Odbcad.exe under the SysWow64 folder on the Server. Select the required driver for SQL Server for the DSN configuration to connect to your database.

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open SystemDSN
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "usp_GetJSON"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append(cmd.CreateParameter("@Ticket_Code",adInteger,adParamInput,100, Request.QueryString("inp_ticket_id")))
cmd.Parameters.Append(cmd.CreateParameter("@Username",adVarchar,adParamInput,100000,user))
cmd.Parameters.Append(cmd.CreateParameter("@OperatorID",adVarchar,adParamInput,100000,oprID))

cmd.Parameters.Append(cmd.CreateParameter("@ReturnJson", adVarchar, adParamOutput,100000))
cmd.Execute

'show alert in case of failure.
ReturnValue = cmd.Parameters("@ReturnJson").Value
if ReturnValue <> " " then
  jsonObject = ReturnValue
  'set the flag as 1 to ensure that JSON has been returned from SP to be used later.
  flag= "1"

end if

The above example shows the SP takes 3 input parameters and 1 output parameter. The SP returns a jsonObject assigned to the ReturnValue.

The ADO Connection Object is used to create an open connection to a data source which lets you access and manipulate a database. The ADO Command object is used to execute a single query against a database using which you can perform CRUD operations.

The json can be generated in the SP using the “FOR JSON PATH” feature for SQL Server. However, json is used just for this example. The return value can be anything as per your requirement.