CODEWITHSUNDEEP
sqlsql-serverstored-proceduresoutput
user1814688
user1814688

Reputation:

SQL Server output param truncated where it is too long

I have 4 stored procedures.

The following is used to be invoked by C#:

ALTER PROCEDURE [dbo].[sp_retrieveResourceShell]
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @result NVARCHAR(MAX)

    EXEC    [dbo].[sp_retrieveResTree]
            @pid = NULL,
            @ret = @result OUTPUT

    ***select*** @result 
END

These are internal stored procedures:

ALTER PROCEDURE [dbo].[sp_retrieveResTree]
    (@pid UNIQUEIDENTIFIER, 
     @ret NVARCHAR(MAX) OUTPUT)
AS
BEGIN
    DECLARE @mdlId UNIQUEIDENTIFIER,
            @mdlGroup NVARCHAR(MAX)
        
    DECLARE mdlCursor CURSOR FOR
        ......

    DECLARE @subRet NVARCHAR(MAX),
            @viewRet NVARCHAR(MAX)

    EXEC [dbo].[sp_retrieveResTree]
            @pid = @mdlId,
            @ret = @subRet OUTPUT

    EXEC dbo.sp_retrieveResView
            @mdlId = @mdlId,
            @ret = @viewRet OUTPUT

     SET @mdlGroup = @mdlGroup + FORMATMESSAGE('{"Id":"%s","Name":"%s","Subs":%s,"Views":%s},'...)
    .......

   SET @ret = FORMATMESSAGE('[%s]',@mdlGroup)
END

ALTER PROCEDURE [dbo].[sp_retrieveResView]
    (@mdlId UNIQUEIDENTIFIER, 
     @ret NVARCHAR(MAX) OUTPUT)
AS
BEGIN
   SET @ret = FORMATMESSAGE('[%s]',@viewGroup)
END

When I invoke sp_retrieveResourceShell like this:

DECLARE @return_value int

EXEC    @return_value = [dbo].[sp_retrieveResourceShell]

SELECT  'Return Value' = @return_value

I can get a truncated string with ellipsis at the string tail. how to get the full string without ellipsis?

update

Changed formatmessage function to string concatenation likes

SET @mdlGroup = @mdlGroup + '{"Id":"' + CAST(@mdlId AS CHAR(36))+ '","Name":"' + @mdlName + '","Subs":' + @subRet + ',"Views":' + @viewRet +'},'

Upvotes: 1

Views: 450

Answers (1)

user1814688
user1814688

Reputation:

when using string concatenation , likes

SET @ret = N'[' + @mdlGroup + N']'

and refernce to [n]varchar(max) + [n]varchar(max)

i got the right full string . Thanks to @MartinSmith at the same time .

Upvotes: 1

Related Questions