Handle Table value type using node REST API + MS-Sql + Express
In my previous blog I have explained that how we can achieve bulk insert with Table value type in Entity framework.
After that when I was working with Node API then also there was a same scenario to insert bulk data from application.
At that time a question was raised in my mind that many of you have also in your mind that
Is it possible to use Table value type in node js?
So the answer is yes we can insert bulk data using table value type in Node js too.
So we are going to implement Table value type in Node JS.
To implement it we just need to follow some steps below.
prerequisites
Step 1:
Create Table value Type
CREATE TYPE [dbo].[Tvp_Employee] AS TABLE(
[Name] [varchar](50) NULL,
[Salary] [numeric](18, 0) NULL
)
GO
Step 2:
After creating table value type. Create stored procedure that will use created Table value type as parameter.
Create PROCEDURE [dbo].[Proc_insertemployee] (@tbl_Employee TVP_EMPLOYEE readonly)
AS
BEGIN
BEGIN try
-- Insert statements for procedure here
INSERT INTO tbl_employee
(NAME,
salary)
SELECT NAME,
salary
FROM @tbl_Employee
Select 1 as 'Code', 'Inserted Successfuly.' as 'Message'
END try
BEGIN catch
DECLARE @ErrorNumber INT
DECLARE @ErrorMessage VARCHAR(2000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorNumber = Error_number(),
@ErrorMessage = 'Error occured at time of inserting'
+ Error_message(),
@Errorseverity = Error_severity(),
@ErrorState = Error_state()
RAISERROR (@Errormessage,@ErrorSeverity,@ErrorState)
END catch
END
Step 3:
Make a SQL table using Node js and respective columns.
It should match the fields of table value type that we created in SQL.
var tvp_Emp = new sql.Table();
// Columns must correspond with type we have created in database.
tvp_Emp.columns.add('Name', sql.VarChar(50));
tvp_Emp.columns.add('Salary', sql.Decimal(5, 0));
Step 4:
Add records in that table
// Add data into the table that will be pass into the procedure
for (var i = 1; i <= 5; i++) {
tvp_Emp.rows.add('MiteshGadhiya_' + i, (10000 * i));
}
Now set that table as input parameter.
request.input('tbl_Employee', tvp_Emp);
Step 6:
Execute store procedure with that input parameter.
//Execute Store procedure
request.execute('Proc_insertemployee', function (err, recordsets, returnValue) {
console.dir(JSON.stringify(recordsets[0][0]));
res.end(JSON.stringify(recordsets[0][0]));
});
Run your API and test it… It will insert all records in database


That’s It and we are done.
Hope this information will be helpful.
Leave a Comment