CODEWITHSUNDEEP
sql-serverdelphistored-proceduresnvarchar
BenjiK
BenjiK

Reputation: 65

Delphi, read SQL Server stored procedure with nvarchar(max) output parameter

Using Delphi XE7 and SQL Server 2008. I have a stored procedure which looks like this:

ALTER PROCEDURE [dbo].[uspUsers_GetAll]
    @ReturnData nvarchar(max) = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @ReturnData = CONVERT(nvarchar(max),
                    (SELECT *, (SELECT UserID, RightType
                                FROM UserRights
                                WHERE UserRights.UserID = Users.ID
                                FOR XML RAW('UserRight'), 
                                ROOT('UserRights'), ELEMENTS, TYPE)
                     FROM Users
                     FOR XML RAW('Users'), ROOT('root') , ELEMENTS));
END;

What I want to do is to return all the users and their rights in an xml converted into a string. The point is that on the client side, will read the string and turn it into an XML document.

I execute the stored procedure using TADOStoredProc like this:

  uspAdoWork.Close;

  if uspAdoWork.Prepared then
     uspAdoWork.Prepared := False;

  uspAdoWork.Parameters.Clear;
  uspAdoWork.ProcedureName := 'uspUsers_GetAll';
  uspAdoWork.Parameters.Refresh;
  uspAdoWork.Prepared := True;

  dmApp.uspAdoWork.ExecProc;

  RetXML := uspAdoWork.Parameters.ParamByName('@ReturnData').Value;

My problem is that the returned value is cut off after 4000 characters. I did read something about this limitation. Is there a way to avoid it (some kind of workaround)?

Upvotes: 2

Views: 1036

Answers (1)

Iverson
Iverson

Reputation: 91

Turn it into a column to avoid parameter issues.

Use this in your SQLDataSet.CommandText or Query.SQL:

DECLARE @json NVARCHAR(MAX);

EXECUTE [dbo].[uspUsers_GetAll] @ReturnData=@json OUTPUT;

SELECT @json AS JSON

Using:

var
  jsonStr: string;
begin
  sqlDataSet1.Open;
  jsonStr := sqlDataSet1.FieldByName('JSON').AsString;

...

Upvotes: 1

Related Questions