Skip to main content

Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft

Network Connection needs

This integration needs network access to the server where the service is running.

See the Network access page for details about how to achieve that.

SQL Server snippets available in editor

note

The title is the triggering text for YepCode to autocomplete the script

Integration

New integration from credential
const mssqlConnectionPool = yepcode.integration.mssql('credential-slug')
New integration from plain authentication data
const mssql = require('mssql');

const mssqlConnectionPool = new mssql.ConnectionPool({
user: 'sa',
password: 'Pass@word',
database: 'master',
server: 'localhost',
pool: {
max: 10,
min: 0,
idleTimeoutMillis: 30000
},
options: {
encrypt: true, // for azure
trustServerCertificate: false // change to true for local dev / self-signed certs
}
});

Connect

Connect
let pool;
try {
pool = await mssqlConnectionPool.connect();
await pool.query('SELECT GETDATE();');
} catch (err) {
console.error(err);
} finally {
pool && pool.close();
}

SELECT Text only

SELECT Text only
try {
const result = await mssqlPool.query(
'SELECT name, price FROM products'
);
result.recordset.forEach((row) => console.log(row));
} catch (err) {
console.error(err);
}

SELECT Parameterized

SELECT Parameterized
try {
const { recordset, rowsAffected } = await mssqlPool
.request()
.input('name_filter', mssql.VarChar(50), 'awesome-product-name')
.query('SELECT * FROM products WHERE name = @name_filter');
console.log('Count:', rowsAffected[0]);
recordset.forEach(({ name, price, stock, created_at: createdAt }) =>
console.log(name, price, stock, createdAt)
);
} catch (err) {
console.error(err);
}

INSERT Text only

INSERT Text only
try {
const { rowsAffected } = await mssqlPool.query(
"INSERT INTO products(name, price, stock, created_at) VALUES('awesome-product-name', 14, 99, CURRENT_TIMESTAMP)"
);
console.log('Inserted rows:', rowsAffected[0]); // 1
} catch (error) {
console.error(error)
}

INSERT Parameterized

INSERT Parameterized
const mssql = require('mssql');

try {
const { rowsAffected } = await mssqlPool
.request()
.input('name', mssql.VarChar(50), 'awesome-product-name')
.input('price', mssql.Int, 14)
.input('stock', mssql.Int, 99)
.query(
'INSERT INTO products(name, price, stock, created_at) VALUES(@name, @price, @stock, CURRENT_TIMESTAMP)'
);
console.log('Inserted rows:', rowsAffected[0]); // 1
} catch (error) {
console.error(error)
}