Question: Stored Procedure with Output parameters of type varchar and returncode

[Trobeloo]

I have a stored procedure with an output parameter and a return value:
ALTER PROCEDURE [dbo].[TESTSP] (@gmnv nvarchar(20) OUTPUT)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @gmnv = 'String100';
Return 123456;

END

I want to retrieve both the output value and the return value. As I can see there is a bug with Output parameters of type varchar #836.

Before that bug is fixed is there an alternative way to do that?

[mikependon]

Your case is returning 2 values and the answer to that is yes, you can hack it via explicit execution approach like below.

var sql = @"DECLARE @gmnv NVARCHAR(20);
     EXEC sp_Test @gmnv OUTPUT;
     SELECT @gmnv;";
using var extractor = connection.ExecuteQueryMultiple(sql);
var returnValue = extractor.Scalar<int>();
var gmnv = extractor.Scalar<string>();

The code is not tested but we did it this way in the past. Hope this helps! 😄

[Trobeloo]

I was not able to use your suggestion but no problem as I cloned and compiled the master branch. It is now working with a DirectionalQueryField and the size property.