Trying to read returned value from stored procedure always shows 0-CodePudding

In SQL Server, I have an existing Document_Add stored procedure that works and returns a good DocID value (in Visual Studio vb code) and cannot change. Calling it like this in SQL:

EXEC @DocID = PADS2.dbo.Document_Add @SystemCode...

This runs the stored procedure, but @DocID is always 0 (whether declared as INT or varchar).

Expecting @DocID to be 2594631 or similar.

Any ideas?

CodePudding user response：

I always say RTFM - read the fine manual provided by microsoft.

CodePudding user response：

Credit to rjs123431:

SQL SP that ends like this:

    ...
    SELECT @Rslt = CONVERT(VARCHAR(2000),@NewId)
    select @Rslt;
END

Returns a string '2680914' in vb:

sDocid = DA.ExecScalarString(EXEC PADS2.DBO.[Document_Add] 'C', ...)

But returns 0 when called from SQL like this:

EXEC @DocID = PADS2.dbo.Document_Add @SystemCode...

This returns correct DocID (2680914) when using a Temp Table like this:

INSERT INTO @TempTable EXEC PADS2.dbo.Document_Add @SystemCode...
set @sDocID = (SELECT DocID FROM @TempTable)