@maderaunified/musd-saml/helpers/sql.js

const mssql = require( 'mssql' );

const config = require( '../config/datawarehouse.mssql' );

/**
 * executeSproc
 *
 * Executes a sproc 🖖🏼. Based on a pre-defined query,
 * known as a "stored procedure", build a request with a
 * set of known parameters to execute on one of the Madera
 * DBs. An engineer may find out what parameters belong to
 * what procedure, but this is not presently documented.
 * When you ask Madera for a procedure to be created, the
 * one who creates the procedure will also outline the
 * requirements for that sproc.
 *
 */

module.exports = function executeSproc( sprocInfo, onRow ) {

	return new Promise( async ( resolve, reject ) => {
		const { parameters, procedure } = sprocInfo;
		const results = [];

		const pool = await new mssql.ConnectionPool( config ).connect();
		const request = await pool.request();
		request.stream = true;

		request.on( 'row', ( row ) => {
			let newRow = row;

			if ( onRow ) {
				newRow = onRow( row );
			}

			results.push( newRow );
		} );

		request.on( 'error', ( error ) => {
			console.error( error );
			reject( error );
		} );

		request.on( 'done', () => resolve( results ) );

		parameters.forEach( ( { name, value } ) => request.input( name, mssql.NVarChar, value ) );

		request.execute( procedure, ( err ) => {

			if ( err ) {
				console.log( err );
				console.log( sprocInfo );
			}

		} );

	} );

};