Sql – Return a value from stored procedure classic asp

asp-classicsqlstored-proceduresvbscript

As a noob to stored procedures, I can't get my head around how to get a classic ASP (vbscript) page to return a value from a stored procedure. I can write data to a table ok, it's just retrieving stuff that has me stuck.

If I use a very basic example. This is a stored procedure that should return a count of the records in a table:

CREATE PROCEDURE [dbo].[SP_RecordCount] 

AS

SELECT COUNT(*) FROM MyTable

What Classic ASP code (and what changes to the SPROC) would I need to write to be able to display the value in a browser?

Once I understand how to get a basic output such as this, I should hopefully(!) be able to build on the knowledge.

Thanks.

Best Answer

Probably the cleanest way is to modify your stored procedure like this:

CREATE PROCEDURE [dbo].[SP_RecordCount] 
@CountResult int OUTPUT
AS

SELECT @CountResult = COUNT(*) FROM MyTable

... and this is the server-side code to call it and read the value:

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = .... previously opened ADO connection here
cmd.CommandType = adCmdStoredProc  'be sure adCmdStoredProc constant is set in scope, or hardcode relevant integer instead'
cmd.CommandText = "SP_RecordCount"
cmd.Parameters.Refresh 

cmd.Execute 
Dim count       
count = cmd.Parameters(1)
Related Topic