Return and Store string from an execute statement to be used later

Question

I've got a procedure call that is used by several groups/processes etc.

The call works as follows:

EXEC LWP_PAYMENT_URL @order_no, @dept

and it returns a string like this

NzI2NzU4NabNzEyMj24Ny1zYQ=

I'm given the assignment to create a url path as follows

DECLARE @url_path VARCHAR(4000) 
SET @url_path = 'https://www.website.com/payment?code=' 

DECLARE @ReturnValue VARCHAR(4000) = ''
EXEC @ReturnValue = LWP_PAYMENT_URL  @order_no, @dept

SET @url_path = @url_path + @ReturnValue
SELECT @ReturnValue, @url_path

My goal is to take the hard coded url_path and get the encoded string from the execute and save it in a variable and concatenate it to the url_path.

What I'm seeing is that the string is returned part of the execute call instead of setting it to @ReturnValue and then looks like I get a zero value being saved and concatenated.

Added these are the final two lines of the LWP_PAYMENT_URL procedure.

DECLARE @Encoded VARCHAR(500) = CONVERT(VARCHAR(500), (SELECT CONVERT(VARBINARY, @string) FOR XML PATH(''), BINARY BASE64))
SELECT  @Encoded AS [Encoded]

Thank you

Answer 1

Your stored procedure should be doing this instead:

CREATE OR ALTER PROCEDURE dbo.LWP_PAYMENT_URL
  ...@input parameters...,
  @encoded varchar(500) = NULL OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  ...
  SET @Encoded = CONVERT(varchar(500),
    (SELECT CONVERT(VARBINARY, @string) FOR XML PATH(''), BINARY BASE64));
END

And then the caller says:

DECLARE @ReturnValue varchar(500);
EXEC dbo.LWP_PAYMENT_URL @order_no, @dept, 
  @Encoded = @ReturnValue output;

If you can't change the stored procedure, create a separate one, or a table-valued UDF as suggested in the comments, or (assuming there are no other SELECTs in the procedure we can't see):

CREATE TABLE #foo(ReturnValue varchar(500));
INSERT #foo EXEC dbo.LWP_PAYMENT_URL ...;
DECLARE @ReturnValue varchar(500);
SELECT @ReturnValue = ReturnValue FROM #foo;

That's gross, though, and basically an abuse of how data sharing should work in SQL Server.

Ideally what you should do is, if the logic is the same for all uses, put that logic in some type of module that is much easier to reuse (e.g. a table-valued function). Then this existing stored procedure can maintain the current behavior (except it would call the function instead of performing the calculation locally), and you can create a different stored procedure (or just call the function directly, if this is all your code is doing), and the logic doesn't have to be duplicated, and you don't have to trample on their stored procedure.