const connection2 = new sql.Connection(config, function(err) {
// ... error checks
// Stored Procedure
const request = connection2.request();
request.input('input_parameter', sql.Int, 10);
request.output('output_parameter', sql.VarChar(50));
request.execute('procedure_name', function(err, recordsets, returnValue) {
// ... error checks
console.dir(recordsets);
});
});
.then(function() {
const value = 50;
// Query
new sql.Request()
.input('input_parameter', sql.Int, value)
.query('select * from mytable where id = @input_parameter').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name')
.then(function(recordsets) {
console.dir(recordsets);
})
.catch(function(err) {
// ... error checks
});
})
function test_table() {
var table = new sql.Table('#temp_table');
table.create = true;
table.columns.add('name', sql.VarChar(sql.MAX), { nullable: false });
table.columns.add('type', sql.Int, { nullable: false });
table.columns.add('amount', sql.Decimal(7, 2), { nullable: false });
table.rows.add('name', 42, 3.50);
table.rows.add('name2', 7, 3.14);
}
sql.connect(config, function(err) {
// ... error checks
// Query
new sql.Request().query('select 1 as number', function(err, recordset) {
// ... error checks
console.dir(recordset);
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name', function(err, recordsets, returnValue) {
// ... error checks
console.dir(recordsets);
});
});
var connection: sql.ConnectionPool = new sql.ConnectionPool(config, function (err: any) {
if (err != null) {
console.warn("Issue with connecting to SQL Server!");
}
else {
connection.query`SELECT ${1} as value`.then(res => { });
var requestQuery = new sql.Request(connection);
var getArticlesQuery = "SELECT * FROM TABLE";
requestQuery.query(getArticlesQuery, function (err, result) {
if (err) {
console.error(`Error happened calling Query: ${err.name} ${err.message}`);
}
// checking to see if the articles returned as at least one.
else if (result.recordset.length > 0) {
}
});
getArticlesQuery = "SELECT 1 as value FROM TABLE";
requestQuery.query<Entity>(getArticlesQuery, function (err, result) {
if (err) {
console.error(`Error happened calling Query: ${err.name} ${err.message}`);
}
// checking to see if the articles returned as at least one.
else if (result.recordset.length > 0 && result.recordset[0].value) {
}
});
var requestStoredProcedure = new sql.Request(connection);
var testId: number = 0;
var testString: string = 'test';
requestStoredProcedure.input('pId', testId);
requestStoredProcedure.input('pString', testString);
requestStoredProcedure.execute('StoredProcedureName', function (err, recordsets, returnValue) {
if (err != null) {
console.error(`Error happened calling Query: ${err.name} ${err.message}`);
}
else {
console.info(returnValue);
}
});
requestStoredProcedure.execute<Entity>('StoredProcedureName', function (err, recordsets, returnValue) {
if (err != null) {
console.error(`Error happened calling Query: ${err.name} ${err.message}`);
}
else {
console.info(returnValue);
}
});
var requestStoredProcedureWithOutput = new sql.Request(connection);
var testId: number = 0;
var testString: string = 'test';
requestStoredProcedureWithOutput.input("name", sql.VarChar, "abc"); // varchar(3)
requestStoredProcedureWithOutput.input("name", sql.VarChar(50), "abc"); // varchar(MAX)
requestStoredProcedureWithOutput.output("name", sql.VarChar); // varchar(8000)
requestStoredProcedureWithOutput.output("name", sql.VarChar, "abc"); // varchar(3)
requestStoredProcedureWithOutput.input("name", sql.Decimal, 155.33); // decimal(18, 0)
requestStoredProcedureWithOutput.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
requestStoredProcedureWithOutput.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
requestStoredProcedureWithOutput.input("name", sql.DateTime2, new Date()); // datetime2(7)
requestStoredProcedureWithOutput.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
requestStoredProcedure.execute('StoredProcedureName', function (err, recordsets, returnValue) {
if (err != null) {
console.error(`Error happened calling Query: ${err.name} ${err.message}`);
}
else {
console.info(requestStoredProcedureWithOutput.parameters['output'].value);
}
});
requestStoredProcedure.execute<Entity>('StoredProcedureName', function (err, recordsets, returnValue) {
if (err != null) {
console.error(`Error happened calling Query: ${err.name} ${err.message}`);
}
else {
console.info(requestStoredProcedureWithOutput.parameters['output'].value);
}
});
}
});
/** Samples from https://github.com/patriksimek/node-mssql#data-types */
import * as sql from "mssql";
const request = new sql.Request();
request.input("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.VarChar(50), "abc"); // varchar(50)
request.input("name", sql.VarChar(sql.MAX), "abc"); // varchar(MAX)
request.output("name", sql.VarChar); // varchar(8000)
request.output("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.Decimal, 155.33); // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
request.input("name", sql.DateTime2, new Date()); // datetime2(7)
request.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
/** Samples from https://github.com/patriksimek/node-mssql#table-valued-parameter-tvp */
import * as sql from "mssql";
const tvp = new sql.Table()
// Columns must correspond with type we have created in database.
tvp.columns.add('a', sql.VarChar(50));
tvp.columns.add('b', sql.Int);
// Add rows
tvp.rows.add('hello tvp', 777); // Values are in same order as columns.
// You can send table as a parameter to stored procedure.
const request = new sql.Request();
request.input('tvp', tvp);
request.execute('MyCustomStoredProcedure', function(err, recordsets, returnValue) {
// ... error checks
console.dir(recordsets[0][0]); // {a: 'hello tvp', b: 777}
});
request.input('input_parameter', value);
request.input('input_parameter', sql.Int, value);
class MyClass {
number: number;
}
sql.map.register(MyClass, sql.Text);
sql.map.register(Number, sql.BigInt);
/**
* output https://github.com/patriksimek/node-mssql#output-name-type-value
*/
request.output('output_parameter', sql.Int);
request.output('output_parameter', sql.VarChar(50), 'abc');
/**
* pipe https://github.com/patriksimek/node-mssql#pipe-stream
*/
const request1 = new sql.Request();
let stream: NodeJS.WritableStream;
request1.pipe(stream);
request1.query('select * from mytable');
stream.on('error', function(err) {
// ...
});
stream.on('finish', function() {
// ...
});
ps.execute({param: 12345}, function(err, recordset) {
// ... error checks
ps.unprepare(function(err) {
// ... error checks
});
});
});
/**
* input https://github.com/patriksimek/node-mssql#input-name-type
*/
ps.input('input_parameter', sql.Int);
ps.input('input_parameter', sql.VarChar(50));
/**
* output https://github.com/patriksimek/node-mssql#output-name-type
*/
ps.output('output_parameter', sql.Int);
ps.output('output_parameter', sql.VarChar(50));
/**
* prepare https://github.com/patriksimek/node-mssql#prepare-statement-callback
*/
const ps2 = new sql.PreparedStatement();
ps2.prepare('select @param as value', function(err) {
// ... error checks