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.

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.

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.

Like this post? Share it!