Using JSON Parameters for SQL Stored Procedures

Does it work?

Image by Gerd Altmann from Pixabay

At my company we use Microsoft SQL Server for data storage, and Stored Procedures (SPs) for interacting with the data. I don’t want to get into the pros and cons of the technology in this article. With the JSON formatted data, some people may wonder why we are not using some kind of NO-SQL approach, but for the purposes of this article I am assuming that MSSQL is the only option available.

I am very interested to hear other people’s views on this approach as it is something I have only recently conceptualised and not really used extensively.

We have often debated the different approaches to managing SPs in our company, particularly relating to adding and removing fields from tables and the impact this has on the SPs.

Let’s take the example of a table to store data about books.

CREATE TABLE [dbo].[book](
  [book_id] [int] IDENTITY(1,1) NOT NULL,
  [book_title] [nvarchar](1000) NOT NULL,
  [book_description] [nvarchar](max) NULL,
  [book_author] [nvarchar](1000) NULL,
  CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED
  ([book_id] ASC)
)
GO

Then we might have a stored procedure to return the book data and one to insert/update a book record. For example: