我有一个功能对于执行存储过程和SQL代码片段非常有效。我想知道是否有人可以帮助我对表值参数进行等效的callProcedure？
我当前的功能如下。
`var exec = function (sql, callback) {
var request = new Request(sql, function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
} else {
console.log(rowCount + ' rows');
}
callback(err, obj);
});
var obj = [];
request.on('row', function (columns) {
var values = {};
columns.forEach(function (column) {
if (column.isNull) {
values[column.metadata.colName] = null;
} else {
values[column.metadata.colName] = column.value;
}
});
obj.push(values);
//console.log(obj);
//console.log(" this is the obj in the row function");
});
conn.execSql(request);
//console.log('obj: ' + obj);
return obj;
function columnMetadata(columnsMetadata) {
columnsMetadata.forEach(function (column) {
//console.log(column);
});
}`
我已经在SQL中完成了此操作，以创建表类型：
`--Create the data type
CREATE TYPE dbo.inventoryRequestType AS TABLE
(
ItemNo int NULL
, Qty int NULL
, Department nvarchar(50) NULL
, RequestedBy nvarchar(100) NULL
)
GO`
并且此存储过程用于使用新的表类型进行插入：
`Create PROCEDURE [dbo].[create_purchasing_list]
-- Add the parameters for the stored procedure here
@purchaseList inventoryRequestType ReadOnly
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Inventory Requests]
(
ItemNo
, Qty
, Department
, RequestedBy
)
SELECT
ItemNo
, Qty
, Department
, RequestedBy
FROM @purchaseList
END`
在SQL中，以下内容可用于执行存储过程：
`DECLARE @purchaseList inventoryRequestType
INSERT INTO @purchaseList
SELECT 4444, 1, 'layup', 'marc'
EXEC Purchasing.dbo.create_purchasing_list @purchaseList
`
--->这是我迷路的地方。我不确定如何使最后一部分与节点/乏味一起工作
这是我想在需要帮助的函数中定义表的示例。
列和行变量将由包含函数定义。
var table = { columns: [ Columns //{name: 'id', type: Types.Int}, //{name: 'firstName', type: Types.nvarchar, length: 50} //{name: 'option', type: Types.Bit} ], rows: [ Rows //[15, 'Bob', true], //[16, 'Rob', false] ] };提前致谢，
渣
最佳答案
我现在有一个功能齐全的解决方案。它非常动态，因此我想分享它，因为我无法在此处的示例中找到解决方案。
这是我的app.js调用的我的db-functions.js文件
var express = require('express');
var tds = require('tedious');
var Request = require('tedious').Request;
var Types = require('tedious').TYPES;
var ConnectionPool = require('tedious-connection-pool');
var poolConfig = {
min: 10,
log: true
};
var config = {
userName: "User",
password: "password",
server: "192.168.1.123", //--------------------{ Production Server }
options:
{
requestTimeout: 30 * 1000,
instanceName: '\InstanceOnServer',
//instanceName: '\SQLServer', // Name of SQL Instance
rowCollectionOnRequestCompletion: true,
database: 'PlantDB',
encrypt: true,
debug: {
data: true, // lots of info generated in console when true
payload: false,
token: false,
packet: true, // was on true
log: true
}
}
};
var pool = new ConnectionPool(poolConfig, config);//multiple connections
pool.acquire(function (err, connection) {
if (err) {
console.log(err);
} else {
console.log('connected');
}
})
//var conn = new tds.Connection(config);
pool.on('error', function (err) {
console.log(err);
});
var conn = new tds.Connection(Config);
function requestDone(rowCount, more) {
console.log(rowCount + "rows------");
console.log(column);
}
function infoError(info) {
console.log(info.number + ' : ' + info.message);
}
function debug(message) {
console.log(message);
}
var exec = function (sql, callback) {
// use this function to execute SELECT statements and Stored Procedures
var request = new Request(sql, function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
} else {
console.log(rowCount + ' rows');
}
callback(err, obj);
});
var obj = []; //holds the data generated by the SQL statement when applicable
request.on('row', function (columns) { //identify individual rows
var values = {}; //insert a value
columns.forEach(function (column) { //identify the column
if (column.isNull) { /*adds a key to the value so that each item in
the object is broken into "Column: Value" pairs*/
values[column.metadata.colName] = null;
} else {
values[column.metadata.colName] = column.value;
}
});
obj.push(values);
});
conn.execSql(request);
//console.log('obj: ' + obj);
return obj;
function columnMetadata(columnsMetadata) {
columnsMetadata.forEach(function (column) {
//console.log(column);
});
}
};
var storedProcedures = {
'purchasing_list': //This is an actual stored procedure that exists in my
//Purchases Database which is used to identify this object
{
'fullName': 'Purchases.dbo.purchasing_list',
//fullname of stored procedure
'variable': 'purchaseList',
//the name of my "Table Type" Variable created in SQL
'Columns' : [
{ 'name': 'ItemNo', 'type': Types.Int }
, { 'name': 'ProductName', 'type': Types.NVarChar }
, { 'name': 'ProductDescription', 'type': Types.NVarChar }
, { 'name': 'UnitOfMeasure', 'type': Types.NVarChar }
, { 'name': 'Qty', 'type': Types.Int }
, { 'name': 'Department', 'type': Types.NVarChar }
, { 'name': 'RequestedBy', 'type': Types.NVarChar }
]
},
'another_stored_procedure':
{
'fullName': 'Purchases.dbo.another_stored_procedure',
'variable': 'purchaseList',
'Columns' : [
{ 'name': 'ItemNo', 'type': Types.Int }
, { 'name': 'ProductName', 'type': Types.NVarChar }
, { 'name': 'ProductDescription', 'type': Types.NVarChar }
, { 'name': 'UnitOfMeasure', 'type': Types.NVarChar }
, { 'name': 'Qty', 'type': Types.Int }
, { 'name': 'Department', 'type': Types.NVarChar }
, { 'name': 'RequestedBy', 'type': Types.NVarChar }
]
}
}
var callProc = function (procedureName, Rows, callback) {
var usefulData = storedProcedures[procedureName];
var message = '';
var table = {
columns: usefulData['Columns'],
rows: Rows
//insert an array of arrays with values defined by storedProcedure
};
var request = new Request(usefulData['fullName'], function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
message = 'There was a problem submitting your request!';
} else {
console.log(rowCount + 'No errors in TVP')
//this will likely return an undefined rowCount since this function
//is generally used to insert and update
message = 'Success'
}
callback(err, message);
});
request.on('doneProc', function (rowCount, more, returnStatus, rows) {
//console.log('Row Count' + rowCount);
//console.log('More? ' + more);
//console.log('Return Status: ' + returnStatus);
//console.log('Rows:' + rows);
})
request.addParameter(usefulData['variable'], Types.TVP, table);
//Add a table type set of parameters to the request to complete the
//transaction
conn.callProcedure(request);
};
var insert = function (sql, callback) {
var request = new Request(sql, function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
callback = err;
} else {
console.log('Insert Statement succeeded');
callback = 'Success';
}
});
conn.execSql(request);
};
var update = function (sql, callback) {
var insert = '';
var request = new Request(sql, function (err, rowCount) {
if (err) {
insert = ('Statement failed: ' + err)
} else {
insert = ('Insert Statement succeeded');
}
callback(err, insert);
});
conn.execSql(request);
};
exports.conn = conn
exports.exec = exec
exports.callProc = callProc
exports.insert = insert
exports.update = update
`
在App.js文件中，我这样加载数据库：
var db = require('./public/javascripts/db-functions');
允许插入或更新多行的callProcedure可以
完成如下：
app.route('/update_purchasing')
.post(function (req, res) {
var Rows = req.body.Rows;
db.callProc('create_purchasing_list', Rows, function (err, success) {
if (err) {
res.json('Error creating purchasing list: ' + err);
} else {
res.json('Successfully updated your purchase list');
}
})
})
根据存储过程中选择的列，行可能看起来像这样：
Rows = [
[0101, 'hair gel', 'makes your hair stick up', 'vat', 1, 'house', 'Bob'],
[0102, 'carbon', 'makes light boats', 'roll', 1, 'Layup', 'Rob']
]`
我如何使用EXEC执行存储过程的示例如下所示：
app.get('/start', function (req, res) {
var sess = req.session;
db.exec("dbo.populate_shipping_schedule" + "parameter1", function(err, schedule) {
if (!err) {
sess.schedule = schedule;
res.render('start', { title: 'Start' });
} else {
console.log('This is an Error: ' + err)
}
})
})
`