How to pass json value to stored procedure in node js

Question

I have a stored procedure which contains one "user defined table type" parameter in Microsoft SQL Server. I am passing "Data Table" as parameter value to the stored procedure in C#. I would like to know how to pass Json data from node js as "Data Table" to this stored procedure.

User-defined table type :

CREATE TYPE [dbo].[TpUserRights] AS TABLE
                                    (
                                        [ConfigId] [int] NULL,
                                        [FormId] [int] NOT NULL
                                    )

Stored procedure:

CREATE PROCEDURE [dbo].[SpMergeUserRights] 
    (@UserRights TpUserRights ReadOnly) 
AS
BEGIN
    MERGE ModuleUserConfig AS [Target]
    USING @UserRights AS [Source] ON [Target].ConfigId = [Source].ConfigId

    WHEN NOT MATCHE BY TARGET
       THEN
           INSERT (ModuleId, FormId, UserId, CreatedBy) 
           VALUES (@ModuleId, [Source].FormId, @UserId, @CreatedBy) 

    WHEN MATCHED
       THEN 
           UPDATE 
           SET FormId = [Source].FormId,
               UserId = @UserId, 
               ModifiedDate = GETDATE(),
               ModifiedBy = @CreatedBy;
END

See https://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program — jdweng, Apr 16 '20 at 16:35
I know how to pass value in store procedure in C# code but my question is how to pass json value to store procedure in node js — Mohammed Ismail, Apr 16 '20 at 16:41
You have to parse the value(s) out of the JSON string. You can using JSON deserialize. — jdweng, Apr 16 '20 at 17:48

score 0 · Answer 1 · answered Apr 16 '20 at 18:44

This is simple in SQL Server 2016 and later. Just pass JSON as a string to SQL Server and parse it to load the TVP on the server. See Work with JSON data. And from JavaScript this is the best way to pass bulk data to SQL Server.

On older SQL Server versions, for clients without Table-Valued Parameter support you can use XML, load a temp table, or just call the procedure like this:

declare @rights as TpUserRights

insert into @rights(ConfigId,FormId) 
values (@ConfigId,@FormId)

exec SpMergeUserRights @rights

binding parameters for @ConfigId and @FormId. And either binding additional parameters for more rows, or calling the procedure multiple times.

How to pass json value to stored procedure in node js

1 Answers1