usp_SQLServerCarpenter_Tools_GenerateTSQLCode_For_Get_Put_APIs

/*
	Author	:	Brahmanand Shukla (SQLServerCarpenter.com)
	Date	:	27-May-2022
	Purpose	:	To generate SQL Code for Get and Put API's. This may be useful if you are developing your own data replication strategy to replicate data across multiple servers.
				The procedure returns the following two columns:
				* GetSQL: T-SQL Code Read the data in JSON format.
				* PutSQL: T-SQL Code to parse the JSON and load in the table.
				*** Note: 
				1) The name and structure (or the schema) of the table on both Source and Destination DB should be same.
				2) text and image type columns can't be replicated through this procedure as JSON doesn't support them.
				The procedure accepts one input parameter i.e. @TableName. Procedure runs for one table at a time. The table name needs to be supplied through this parameter.
	Example:
	EXEC [dbo].[usp_SQLServerCarpenter_Tools_GenerateTSQLCode_For_Get_Put_APIs]
		@TableName	=	'Customer_Staging'
*/
CREATE OR ALTER PROCEDURE [dbo].[usp_SQLServerCarpenter_Tools_GenerateTSQLCode_For_Get_Put_APIs]
(
	@TableName		SYSNAME
)
AS
	BEGIN
		SET NOCOUNT ON
		
		DECLARE @GetSQL				NVARCHAR(MAX)
			, @PutSQL				NVARCHAR(MAX)
		DECLARE @Columns			NVARCHAR(MAX)
			, @ColumnsForSelectGet	NVARCHAR(MAX)
			, @ColumnsForSelectPut	NVARCHAR(MAX)
			, @WithClause			NVARCHAR(MAX)
		SELECT @Columns				=	ISNULL(@Columns, '') + ', ' + COALESCE(QUOTENAME(COLUMN_NAME) + CHAR(10) + CHAR(9), ', ') 
			, @ColumnsForSelectGet	=	ISNULL(@ColumnsForSelectGet, '') + ', ' 
											+ COALESCE(	CASE 
															WHEN DATA_TYPE = 'varbinary' 
																THEN 'CONVERT(nvarchar(max), ' + QUOTENAME(COLUMN_NAME) + ', 1' + ') AS ' + QUOTENAME(COLUMN_NAME)
															WHEN DATA_TYPE = 'binary' 
																THEN 'CONVERT(nvarchar(max), ' + QUOTENAME(COLUMN_NAME) + ', 1' + ') AS ' + QUOTENAME(COLUMN_NAME)
															ELSE QUOTENAME(COLUMN_NAME)
														END
											+ CHAR(10) + CHAR(9), ', ') 
			, @ColumnsForSelectPut	=	ISNULL(@ColumnsForSelectPut, '') + ', ' 
											+ COALESCE(	CASE 
															WHEN DATA_TYPE = 'varbinary' 
																THEN 'CONVERT(varbinary(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '), '  + QUOTENAME(COLUMN_NAME) + ', 1' + ')' 
															WHEN DATA_TYPE = 'binary' 
																THEN 'CONVERT(binary(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '), ' + QUOTENAME(COLUMN_NAME) + ', 1' + ')' 
															ELSE QUOTENAME(COLUMN_NAME)
														END
											+ CHAR(10) + CHAR(9), ', ') 
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @TableName
			AND DATA_TYPE NOT IN ('text', 'image')
		SET @Columns = RIGHT(LEFT(@Columns, (LEN(@Columns) - 2)), (LEN(LEFT(@Columns, (LEN(@Columns) - 2))) - 2))
		SET @ColumnsForSelectGet = RIGHT(LEFT(@ColumnsForSelectGet, (LEN(@ColumnsForSelectGet) - 2)), (LEN(LEFT(@ColumnsForSelectGet, (LEN(@ColumnsForSelectGet) - 2))) - 2))
		SET @ColumnsForSelectPut = RIGHT(LEFT(@ColumnsForSelectPut, (LEN(@ColumnsForSelectPut) - 2)), (LEN(LEFT(@ColumnsForSelectPut, (LEN(@ColumnsForSelectPut) - 2))) - 2))
		SELECT @WithClause	=	ISNULL(@WithClause, '') + ', ' 
									+ COALESCE(QUOTENAME(COLUMN_NAME) + CHAR(9) + CHAR(9) + CHAR(9)
									+	CASE 
											WHEN DATA_TYPE = 'varbinary' 
												THEN 'nvarchar' 
											WHEN DATA_TYPE = 'binary' 
												THEN 'nvarchar'
											ELSE QUOTENAME(DATA_TYPE)
										END 
									+	CASE 
											WHEN DATA_TYPE IN ('varchar', 'char', 'nchar', 'nvarchar' ) 
												THEN
													CASE
														WHEN (CHARACTER_MAXIMUM_LENGTH = -1)
															THEN '(MAX)'
														ELSE '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
													END												
											WHEN DATA_TYPE IN ('numeric', 'decimal' )
											    THEN '(' + CAST( ISNULL(NUMERIC_PRECISION, 0) AS VARCHAR ) + ',' + CAST( ISNULL(NUMERIC_SCALE, 0) AS VARCHAR) + ')'
											WHEN DATA_TYPE = 'varbinary' 
												THEN '(MAX)' 
											WHEN DATA_TYPE = 'binary' 
												THEN '(MAX)'
											ELSE '' 
										END 
									+ CHAR(10) + CHAR(9), ', ') 
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @TableName
			AND DATA_TYPE NOT IN ('text', 'image')
		SET @WithClause =	'WITH' + CHAR(10) + '(' + CHAR(10) + CHAR(9) 
							+ RIGHT(LEFT(@WithClause, (LEN(@WithClause) - 2)), (LEN(LEFT(@WithClause, (LEN(@WithClause) - 2))) - 2)) + CHAR(10) 
							+ ')'
		SET @GetSQL	=	'SELECT ' + @ColumnsForSelectGet + CHAR(10) 
						+ 'FROM ' + @TableName + ' WITH(NOLOCK)' + CHAR(10) 
						+ 'FOR JSON PATH'
		SET @PutSQL	=	'INSERT INTO ' + @TableName + CHAR(10) + ' (' + CHAR(10) + CHAR(9) + @Columns + ' )' + CHAR(10) 
						+ 'SELECT ' + @ColumnsForSelectPut + CHAR(10) 
						+ 'FROM OPENJSON(@JSON)' + CHAR(10)  
						+ @WithClause + ' tbl'
		SELECT CAST(@GetSQL AS XML) AS [GetSQL], CAST(@PutSQL AS XML) AS [PutSQL]
	END