How to Use SQL Out Variables in Classic ASP

This blog post is for those lucky souls programming in Classic ASP VBScript who need to access output variables in Microsoft SQL (T-SQL) stored procedures. In T-SQL, stored procedures can have out parameters (analogous to OUT parameters in .NET). This can be handy when only a scalar result is required, or maybe a small set of disjointed scalar results are needed and the programmer wants to avoid iterating between recordsets.

This example will use a simple three argument stored procedure where the first two arguments are for the strings to be concatenated and the third argument is the result.

First, creating the stored procedure and the proof of concept that the stored procedure works.

/*
Note this removes the stored procedure if it exists but assumes any object named tmpSprocDemoConCat is a stored procedure.
*/

if object_id(N'dbo.tmpSprocDemoConCat') is not null drop procedure dbo.[tmpSprocDemoConCat]

go

create proc dbo.[tmpSprocDemoConCat]
(
	@string1 nvarchar(100) -- input variable in first position
,	@string2 nvarchar(100) -- input variable in first position
,	@outMessage nvarchar(200) output -- note the output keyword after the variable type
)
as

set @outMessage = coalesce(@string1,'') + coalesce(@string2,'');

go

declare 
	@string1 nvarchar(5)
,	@string2 nvarchar(5)
,	@outMessage nvarchar(10)

set @string1='Part1';
set @string2='Part2';

exec [tmpSprocDemoConCat] @string1, @string2, @outMessage out; -- note use of out keyword again

print @outMessage;

If you run this in SQL Server Management Studio you will see the output message “Part1Part2” and the stored procedure will exist for our Classic ASP testing.

This is the VBScript Classic ASP code demonstrating use of the OUT parameter functionality.

<%@ Language=VBScript %>
<%
Server.ScriptTimeout = 720
Response.CacheControl = "no-cache" 
Response.ContentType = "text/plain"
Response.AddHeader "Pragma", "no-cache" 
Response.Expires = -1 

Const adVarChar = 200
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdStoredProc = &H0004

Sub OpenDatabase (byref pConn)	
	dim connStr
	connStr = "Provider=SQLOLEDB.1;Data Source=yourServerLocation;Initial Catalog=YourDatabaseName;User ID=yourUserId;Password=yourPassword"
	Set pConn = Server.CreateObject("ADODB.Connection")
	pConn.ConnectionTimeout = 120
	pConn.CommandTimeout = 120
	
	pConn.Open connStr	
End Sub

Sub CloseDatabase (byref pConn)
	if pConn.State = 1 then  '-- if open
		pConn.close
	end if	
	Set pConn = Nothing
End Sub

OpenDatabase dbCon

Private Function ConcatUsingSql(dbCon,string1,string2)
    Dim dbCommand, strMessageOut
	Set dbCommand = server.CreateObject("ADODB.Command")
	dbCommand.ActiveConnection = dbCon
	dbCommand.CommandText = "dbo.tmpSprocDemoConCat"
	With dbCommand
		.CommandType = adCmdStoredProc
		.Parameters.Append .CreateParameter("@string1", adVarChar, adParamInput, 100, string1)
		.Parameters.Append .CreateParameter("@string2", adVarChar, adParamInput, 100, string2)
		.Parameters.Append .CreateParameter("@outMessage", adVarChar, adParamOutput, 200, strMessageOut)
		.Execute
		strMessageOut = .Parameters(2).Value ' Zero based so "2" is the third parameter
	End With
    ConcatUsingSql = strMessageOut
End Function

response.write ConcatUsingSql(dbCon,"This is ","concatenation")

CloseDatabase dbcon
%>

Line 16 has the connection string you will need to update with the proper values.

The function ConcatUsingSql is pretty simple and you should see that calling a stored procedure seeking the result of an output argument is almost as easy as calling a stored procedure seeking on result at all. No effort is required to access recordset information. Instead, simply set the parameter direction to adParamOutput (a constant with a value of &H0002), execute the stored procedure and get that value.

Just remember, get the value of the output variable AFTER you execute the stored procedure.