http://jpablobr.com

There’s not much -or useful- information about how to call Stored Procedures (SPROCS) using jscript. Therefore, this is the very very basic way to do it. I will just explain what the following script is doing.

First things first… the regular jscript function name declaration followed by a “try…catch…finally Statement” where I’m setting up the connection string. One of the hardest parts from using ADO is getting the connection string right. You can get this Information from the developer of the project you’re working on, or look at http://www.connectionstrings.com.

function usp_ExternalSPROC(){

try {

var AConnection, Tbl, Field_NameR;

// Create a new Connection object

AConnection = ADO.CreateConnection();

AConnection.ConnectionString =

“Provider=SQLOLEDB.1;”+

“Persist Security Info=False;”+

“User ID=**User_Name**;”+

“pwd=**User_Pass**;”+

“Initial Catalog=**DataBase**;”+

“Data Source=**SQL_Instance**”;

Alright cool! Now that we have a valid connection string we should activate the connection.

AConnection.Open();

Here we are going to set “Tbl” to execute the SPROC using the “AConnection” that we created previously.

Tbl = AConnection.Execute(“[**DataBase**].[dbo].[usp_ExternalSPROC]“);

Then with the while loop we are scanning all the records till the End-of-file (EOF) and print the information on those fields.

// Scans all records returned by the SPROC

Tbl.MoveFirst();

while (!Tbl.EOF){

// Gets the field values from the ORDER BY

Field_NameR = Tbl.Fields(“Field_Name”).Value;

Log.Message(Field_NameR);

Tbl.MoveNext();

}

Finally we close the connection with the DB.

// Close connection