Question 135578 in Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – Trying to read returned value from stored procedure always shows 0

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    <If this is not the place to follow up, please advise.>

    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)

  2. 0

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

    https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver16

    1 - Example D shows how to use input parameters.
2 - Example F show how to use output parameters.

    Just remember, input and output parameters are scalar, not tables.

    If you do need to input of a table look at Example G.

    3 - Example G - how to pass a table value parameter
4 - Example B - return multiple result sets.

    If you execute two SELECT statements in the procedure, you will have MARS (multiple active result sets).

    By default, a procedure returns a value of zero. It is typical used to indicate if the procedure was a success. You can code a non zero value to indicate an error.

    Always read the docs!

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search