T-SQL procedure to get a JSON object based on the resultset of a SQL query

pGetJSONfromQuery.sql

//Gets the result of the query @ParameterSQL and returns it as a single-dimension JSON object
//If variable @VarName is passed, the resultant output assigns the object to that variable

CREATE PROCEDURE [dbo].[pGetJSONFromQuery] @ParameterSQL NVARCHAR(4000), @VarName VARCHAR(255) = NULL
AS
BEGIN

	DECLARE @SQL NVARCHAR(1000)	
	DECLARE @XMLString VARCHAR(MAX)
	DECLARE @XML XML;
	DECLARE @Paramlist NVARCHAR(1000)

	SET @Paramlist = N'@XML XML OUTPUT'

	SET @SQL = 'WITH PrepareTable (XMLString)'
	SET @SQL = @SQL + 'AS('
	SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW,TYPE,ELEMENTS '
	SET @SQL = @SQL + ')'
	SET @SQL = @SQL + ' SELECT @XML=[XMLString] FROM [PrepareTable] '

	EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
	SET @XMLString=CAST(@XML AS VARCHAR(MAX))
	  
	DECLARE @JSON VARCHAR(MAX)
	DECLARE @Row VARCHAR(MAX)
	DECLARE @RowStart INT
	DECLARE @RowEnd INT
	DECLARE @FieldStart INT
	DECLARE @FieldEnd INT
	DECLARE @KEY VARCHAR(MAX)
	DECLARE @Value VARCHAR(MAX)
	  
	DECLARE @StartRoot VARCHAR(100) = '<row>'
	DECLARE @EndRoot VARCHAR(100) = '</row>'
	DECLARE @StartField VARCHAR(100) = '<'
	DECLARE @EndField VARCHAR(100) = '>'
	  
	SET @RowStart = CHARINDEX(@StartRoot,@XMLString,0)
	SET @JSON = N''
	WHILE @RowStart > 0
	BEGIN
		SET @RowStart = @RowStart + LEN(@StartRoot)
		SET @RowEnd = CHARINDEX(@EndRoot, @XMLString, @RowStart)
		SET @Row = SUBSTRING(@XMLString, @RowStart, @RowEnd - @RowStart)
		SET @JSON = @JSON + '{'
	  
		-- for each row
		SET @FieldStart = CHARINDEX(@StartField, @Row, 0)
		WHILE @FieldStart > 0
		BEGIN
			-- parse node key
			SET @FieldStart = @FieldStart + LEN(@StartField)
			SET @FieldEnd = CHARINDEX(@EndField,@Row,@FieldStart)
			SET @KEY = SUBSTRING(@Row, @FieldStart, @FieldEnd-@FieldStart)
			SET @JSON = @JSON + '"' + @KEY + '":'
			-- parse node value
			SET @FieldStart = @FieldEnd + 1
			SET @FieldEnd = CHARINDEX('</', @Row, @FieldStart)
			SET @Value = SUBSTRING(@Row, @FieldStart, @FieldEnd-@FieldStart)
			SET @JSON = @JSON + '"' + @Value + '", '
	  
			SET @FieldStart = @FieldStart + LEN(@StartField)
			SET @FieldEnd = CHARINDEX(@EndField, @Row, @FieldStart)
			SET @FieldStart = CHARINDEX(@StartField, @Row, @FieldEnd)
		END    
		IF LEN(@JSON) > 0 SET @JSON = SUBSTRING(@JSON, 0, LEN(@JSON))
		SET @JSON=@JSON+'},'+ CHAR(13) + CHAR(10)
		--/ for each row
	  
		SET @RowStart=CHARINDEX(@StartRoot,@XMLString,@RowEnd)
	END
	IF LEN(@JSON) > 0 SET @JSON = SUBSTRING(@JSON,0,LEN(@JSON)-2)
	SET @JSON='['+@JSON+']'
	
    SELECT  Json = ISNULL('var ' + @VarName + ' = ', N'') + @JSON, lEN(@JSON) AS Length;

END

GO