This project contains a set of T-SQL scripts that generate:
- Stored procedures that INSERT, UPDATE, or MERGE input JSON text into table
- Stored procedures that generate SELECT statements that generate JSON from SQL tables.
About this sample
Setup
Generate
Modify generated source
- Applies to: SQL Server 2016 (or higher), Azure SQL Database
- Programming Language: Transact-SQL
- Authors: Jovan Popovic
To generate procedures, apply script in generate-json-crud.sql file.
** GENERATE CRUD Functions for WWI tables. **
declare @SchemaName sysname = 'Application' --> Name of the table where we want to insert JSON
declare @TableName sysname = 'People' --> Name of the table schema where we want to insert JSON
declare @JsonColumns nvarchar(max) = '|CustomFields|' --> List of pipe-separated NVARCHAR(MAX) column names that contain JSON text
declare @IgnoredColumns nvarchar(max) = N'LastEditedBy' --> List of comma-separated columns that should not be imported
print (codegen.GenerateJsonCreateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonRetrieveProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpdateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpsertProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
GO
declare @SchemaName sysname = 'Sales' --> Name of the table where we want to insert JSON
declare @TableName sysname = 'Orders' --> Name of the table schema where we want to insert JSON
declare @JsonColumns nvarchar(max) = '||' --> List of pipe-separated NVARCHAR(MAX) column names that contain JSON text
declare @IgnoredColumns nvarchar(max) = N'LastEditedBy,LastEditedWhen' --> List of comma-separated columns that should not be imported
print (codegen.GenerateJsonCreateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonRetrieveProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpdateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpsertProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
GO
declare @SchemaName sysname = 'Application' --> Name of the table where we want to insert JSON
declare @TableName sysname = 'Countries' --> Name of the table schema where we want to insert JSON
declare @JsonColumns nvarchar(max) = '||' --> List of pipe-separated NVARCHAR(MAX) column names that contain JSON text, e.g. '|AdditionalContactInfo|Demographics|'
declare @IgnoredColumns nvarchar(max) = N'LastEditedBy' --> List of comma-separated columns that should not be imported
print (codegen.GenerateJsonCreateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonRetrieveProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpdateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpsertProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
GO
declare @SchemaName sysname = 'Application' --> Name of the table where we want to insert JSON
declare @TableName sysname = 'Cities' --> Name of the table schema where we want to insert JSON
declare @JsonColumns nvarchar(max) = '||' --> List of pipe-separated NVARCHAR(MAX) column names that contain JSON text, e.g. '|AdditionalContactInfo|Demographics|'
declare @IgnoredColumns nvarchar(max) = N'Location,LastEditedBy' --> List of comma-separated columns that should not be imported
print (codegen.GenerateJsonCreateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonRetrieveProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpdateProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpsertProcedure('Website', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
GENERATE CRUD Functions for Custom table.
DROP TABLE IF EXISTS dbo.Product
GO
CREATE TABLE dbo.Product(
ProductID int IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
Color nvarchar(15) NULL,
Size nvarchar(5) NULL,
Price money NOT NULL,
[Special JSON chars: " \ /
] int NULL,
[Special sql chars [[ " ]]
] int NULL,
Data nvarchar(4000) NULL,
Tags nvarchar(4000) NULL,
DateCreated datetime2 NOT NULL DEFAULT(GETDATE())
)
GO
declare @SchemaName sysname = 'dbo' --> Name of the table where we want to insert JSON
declare @TableName sysname = 'Product' --> Name of the table schema where we want to insert JSON
declare @JsonColumns nvarchar(max) = '|Data|Tags|' --> List of pipe-separated NVARCHAR(MAX) column names that contain JSON text, e.g. '|AdditionalContactInfo|Demographics|'
declare @IgnoredColumns nvarchar(max) = N'DateCreated' --> List of comma-separated columns that should not be imported
print (codegen.GenerateJsonCreateProcedure('dbo', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonRetrieveProcedure('dbo', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpdateProcedure('dbo', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
print (codegen.GenerateJsonUpsertProcedure('dbo', @SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
You can create your own functions codegen.GenerateProcedureHead and codegen.GenerateProcedureTail to generate custom header and footer for every generated procedure, e.g.:
DROP FUNCTION IF EXISTS codegen.GenerateProcedureHead
GO
CREATE FUNCTION codegen.GenerateProcedureHead(@Table sysname, @JsonParam sysname)
RETURNS NVARCHAR(max)
AS BEGIN
Declare @ret nvarchar(max) = '
SET XACT_ABORT ON;
DECLARE @HelpMessage nvarchar(max) = N''JSON '+ @Table +' data is invalid.
Execute SELECT TOP 1 * FROM ' + @Table + ' FOR JSON PATH to see an example of required JSON structure.'';
IF ISJSON('+ @JsonParam + ') = 0
BEGIN
PRINT @HelpMessage;
THROW 51000, N'''+ @JsonParam + ' must be valid JSON data'', 1;
RETURN 1;
END;
BEGIN TRY
BEGIN TRAN;
';
RETURN @ret
END
GO
GO
DROP FUNCTION IF EXISTS codegen.GenerateProcedureTail
GO
CREATE FUNCTION codegen.GenerateProcedureTail(@Table sysname)
RETURNS NVARCHAR(max)
AS BEGIN
Declare @ret nvarchar(max) = '
IF @@ROWCOUNT = 0
BEGIN
PRINT N''Warning: No valid '+@Table+' data found'';
PRINT @HelpMessage;
END;
COMMIT;
END TRY
BEGIN CATCH
PRINT @HelpMessage;
PRINT ERROR_MESSAGE();
THROW 51000, N''Valid JSON was supplied but does not match the '+@Table+' array structure'', 2;
IF XACT_STATE() <> 0 ROLLBACK TRAN;
RETURN 1;
END CATCH;
';
RETURN @ret
END
GOThis project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.
These samples and templates are all licensed under the MIT license. See the license.txt file in the root.
Email questions to: [sqlserversamples@microsoft.com](mailto: sqlserversamples@microsoft.com).