Hemos indagando en diferentes foros para así tenerte la solución para tu problema, si continúas con alguna difcultad puedes dejar tu duda y contestamos porque estamos para servirte.
Solución:
Realmente no lo recomendaría, hay formas mucho mejores de hacer esto en la capa de aplicación, pero lo siguiente evita bucles y es mucho menos detallado que su método actual:
CREATE PROCEDURE dbo.GetJSON @ObjectName VARCHAR(255), @registries_per_request smallint = null
AS
BEGIN
IF OBJECT_ID(@ObjectName) IS NULL
BEGIN
SELECT Json = '';
RETURN
END;
DECLARE @Top NVARCHAR(20) = CASE WHEN @registries_per_request IS NOT NULL
THEN 'TOP (' + CAST(@registries_per_request AS NVARCHAR) + ') '
ELSE ''
END;
DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + @Top + '* INTO ##T ' +
'FROM ' + @ObjectName;
EXECUTE SP_EXECUTESQL @SQL;
DECLARE @X NVARCHAR(MAX) = '[' + (SELECT * FROM ##T FOR XML PATH('')) + ']';
SELECT @X = REPLACE(@X, '<' + Name + '>',
CASE WHEN ROW_NUMBER() OVER(ORDER BY Column_ID) = 1 THEN '{'
ELSE '' END + Name + ':'),
@X = REPLACE(@X, '' + Name + '>', ','),
@X = REPLACE(@X, ',', ', '),
@X = REPLACE(@X, ',]', ']')
FROM sys.columns
WHERE [Object_ID] = OBJECT_ID(@ObjectName)
ORDER BY Column_ID;
DROP TABLE ##T;
SELECT Json = @X;
END
Nota: he cambiado el nombre de su objeto de dos partes (@schema y @table) para aceptar el nombre completo del objeto.
Ejemplo en SQL Fiddle
La idea es básicamente usar la extensión XML dentro de SQL-Server para convertir la tabla en XML, luego simplemente reemplazar las etiquetas de inicio con {ColumnName:
y las etiquetas finales con ,
. Luego, se requieren dos reemplazos más para dejar de agregar el corchete de cierre a la última columna de cada fila y eliminar el final ,
desde el JSON string.
Use de las palabras mágicas For JSON
ejemplo:
SELECT name, surname
FROM emp
FOR JSON AUTO
resultado:
["name": "John", "name": "Jane", "surname": "Doe"]
más info en:
https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017&viewFallbackFrom= sql-server-2014
Nos encantaría que puedieras comunicar esta división si te ayudó.