1. NodeJS to MSSQL - SP with JSON - CRUD | Nodejs API

Explanation: NodeJS code to connect MSSQL with CRUD operation.
DB: Stored Procedure, Table structure explaining.
NodeJS: Routes, Controller, Service & DB Config coding explaining
SP: Stored procedure with JSON param with CRUD operation
Nodejs API

Note: Rename folder name "controller" to "controllers"

PlayList: (MEAN Stack Guide)

PlayList: (Angular Guide)

PlayList: (Real-Time Project Design)

Table Creation:
~~~~~~~~~~
CREATE TABLE Department (
DeptSeqId INT NOT NULL IDENTITY PRIMARY KEY,
DeptId INT,
DeptName NVARCHAR(50) NOT NULL,
IsActive BIT
);

Stored Procedure:
~~~~~~~~~~~~
CREATE PROCEDURE [dbo].[USP_Department_Crud_M]
(
@ActionFlag varchar(15),
@Data varchar(max) = NULL
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @NewMaxId INT;

-- Variables For Table Field
DECLARE @DeptId int;
DECLARE @DeptName nvarchar(50);
DECLARE @IsActive bit;

-- Parse JSON and Assign Value To Table Field Variables
SELECT
@DeptId = DeptId,
@DeptName = DeptName,
@IsActive = IsActive
WITH (
DeptId int,
DeptName nvarchar(50),
IsActive bit
);

BEGIN
SELECT a.DeptId, a.DeptName, a.IsActive
FROM Department a WITH (NOLOCK)
WHERE a.IsActive=1
ORDER BY a.DeptName DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
END

BEGIN
SELECT a.DeptId, a.DeptName, a.IsActive
FROM Department a WITH (NOLOCK)
WHERE a.IsActive=1
AND a.DeptId = @DeptId
FOR JSON PATH, INCLUDE_NULL_VALUES;
END
BEGIN
BEGIN
SELECT @NewMaxId = (COALESCE(MAX(DeptId),0) + 1) FROM Department;

INSERT INTO Department (DeptId, DeptName, IsActive)
END
ELSE
BEGIN
UPDATE Department
SET
DeptName = @DeptName,
IsActive = @IsActive
WHERE DeptId = @DeptId;

SET @NewMaxId = @DeptId;
END

--Output Display
SELECT 'Success' [Status],
'Record Saved Successfully' [Msg],
'200' [ErrorCode],
@NewMaxId [ID] FOR JSON PATH
END
BEGIN
---Before Delete check Whether it is used or not.
UPDATE Department
SET
IsActive = 0
WHERE DeptId = @DeptId;

--Output Display
SELECT 'Success' [Status],
'Record Deleted Successfully' [Msg],
'200' [ErrorCode],
@DeptId [ID] FOR JSON PATH
END

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 'Error' [Status],
ERROR_MESSAGE() [Msg],
CAST(ERROR_NUMBER() AS NVARCHAR(50)) [ErrorCode],
0 [ID]
FOR JSON PATH

END CATCH
END

[VAPStudent]

Note: Rename folder name "controller" to "controllers"

[mangalamganesan]

@VAP Student
Could u please share the link of the repo?

[sanakhan]

Amazing video sir after going through my issue this video's is very help full thanks for making such videos,

Actually i have done NodeJs from YouTube,

I want learn NodeJS with MS-SQL, do you or any friends have mega files of course for
Advance understanding,

Like how to connect 4 5 tables and show data in array of objects format

How to make nested query or subquires like...

I mean if wanna do table match with two table product and order

Product ID with Order table by matching product ID

data {
productId : 1:
productName : "abc",
[{
orderId : 1
orderName : "xyz"
},
{
orderId : 2
orderName : "pqr"
}
]
}
Thanks in advance

[software_engineer_semh]

supper anna and node js to mysql connect pannuradu solli thangale please

[kbalandin]

not sure what language this guy speaks.