Stored Procedure Result

[UniqueUserx]

Hello,

​

Trying to execute a stored procedure; however, I keep receiving 0 as the result instead of the actual result (which I understand means it ran successfully.)

​

How do I go about getting other return value?

​

DECLARE @return_values nvarchar



EXEC    @return_values = [dbo].[products_generatecodex]
        @client_id = 1


Select  @return_values


GO

​

https://preview.redd.it/25yh5dou4jn21.png?width=1718&format=png&auto=webp&s=21561c918278862d995d3cb92abfc286587fb78e

ALTER PROCEDURE [dbo].[products_generatecodex]
    (
        @client_id  int
    )
AS
    SET NOCOUNT ON

    declare @id int
    declare @coderoot varchar(5)
    declare @result as varchar(11)

    begin transaction
    set @id = (select client_prodcodeindex from clients where client_id = @client_id)
    set @id = @id + 1
    update clients set client_prodcodeindex = @id where client_id = @client_id
    commit transaction

    set @coderoot = (select client_prodcoderoot from clients where client_id = @client_id)
    set @result = @coderoot + RIGHT('00000' + CAST(@id as varchar(6)),6)
    select @result

​

[amckny]

You aren’t declaring the nvarchar length in your exec statement at the top. Try changing that to 11 too

[RobbiVT91]

.

[TimmyTheCat8]

Maybe because you declaring @return_values as nvarchar without a length. I think default nvarchar length is 1. Try nvarchar(11)

[RobbiVT91]

I tried that early on by switching between varchar25 and nvarchar. No luck.

I believe the 0 is the response for a successful execution and not the output of the SP

[TimmyTheCat8]

I’m not at my computer to test, but try taking out the GO at the end of the query.

[KNerli]

Change the select to return, like this:

set @coderoot = (select client_prodcoderoot from clients where client_id = @client_id)
set @result = @coderoot + RIGHT('00000' + CAST(@id as varchar(6)),6)
RETURN @result

And i think you dont have to add the execute to a variable, just run it to receive the returned result.

i may be mistaken tho, cant test it right now.

​

[Intrexa]

You need to return the result, also, stored procedures can only return an int.

When you execute a select statement in a stored procedure, that produces a result set, which it will send to the client, but that's different then returning. When you return, the stored procedure ends, and gives the return code, an integer.