0

I am trying to create an SP that will return JSON data from a specified table. I want to store this in a variable to format and concat additional information.

This works well when the dataset is small. However, this SP is only returning the first 8000 characters when large. Is there a way to use the same SP to return longer strings?

Using Azure Studio and just executing the select returns string longer than 8000, but there is no way to store in a variable.

CREATE OR ALTER PROCEDURE [dbo].[spGetJsonFromTable]
(
    @tableName VARCHAR(128),
    @jsonString VARCHAR(MAX) OUTPUT
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000);

    SET @SQL = N'SET @jsonString = (SELECT * FROM ' + @tableName + N' FOR JSON AUTO, INCLUDE_NULL_VALUES);';
    EXEC [sys].[sp_executesql] @SQL,
                               N'@jsonString VARCHAR(MAX) OUT',
                               @jsonString OUT;
END;
GO

This is the call to SP

DECLARE @jsonString VARCHAR(MAX);
EXEC [dbo].[spGetJsonFromTable] @tableName = 'mytable',       -- varchar(128)
                                @jsonString = @jsonString OUTPUT; -- varchar(max)
DennisT
  • 45
  • 6
  • 1
    What does `SELECT DATALENGTH(@jsonString)` return after this? Many and varied are the ways of string truncation, so the first order of business is to eliminate a client-side issue. Do note that trying to package an entire table as one giant JSON string is inherently very inefficient on the server side and will not scale if your table grows to any appreciable size; streaming the table contents into JSON on the client end is going to outpace this approach quickly. – Jeroen Mostert Mar 31 '23 at 16:49
  • One thing is your SP declare is NVARCHAR(MAX) and your Declare to call the SP is VARCHAR(MAX). Update the SP call to NVARCHAR(MAX) and test. – Brad Mar 31 '23 at 17:07
  • You need to show a repro. I don't see any truncation https://dbfiddle.uk/AkaAFemm – Martin Smith Mar 31 '23 at 19:50
  • @MartinSmith - using dbFiddle, print the jsonstring. This is similar behavior in both SSMS and Azure Studio. – DennisT Mar 31 '23 at 20:23
  • 1
    You didn't answer Jeroen's `SELECT DATALENGTH(@jsonString)` question. And, you said `PRINT`. In SSMS > Tools > Options > Query Results > SQL Server > Results to Text there's a "Maximum number of characters displayed in each column" setting which also affects the maximum length of `PRINT` output. So what's the equivalent setting in your ADS client? – AlwaysLearning Apr 01 '23 at 01:03

0 Answers0