Post reply

SET the result of a Strore procedure to a var

  • June 30, 2005 at 1:06 pm

    #164771

    I have the next SP:

    CREATE PROCEDURE SP_NEXTUSER AS

    --ME REGRESA EL SIGUIENTE NUMERO MAS PEQUEÑO DESOCUPADO PARA UN USER_NUMBER

    -- EJEMPLO SI TENGO DADOS DE ALTA 1,2,7,21... ME REGRESARIA UN 3

    DECLARE @NEXTUSERNUM AS smallint

    SET NOCOUNT ON

    select @NEXTUSERNUM= min(num) from

    (SELECT USER_NUM+1 as num

    FROM tbUSERS ) tb

    left outer join tbUSERS on tb.num=tbUSERS.user_num

    WHERE tbUSERS.USER_NUM IS NULL

    RETURN @NEXTUSERNUM

    GO

     

    and I want to put the result of this SP in a var, I do the next put I received an error.

    DECLARE @RETURNVAL AS smallint

    SET @RETURNVAL=SP_NEXTUSER

    Server: Msg 207, Level 16, State 3, Line 2

    Invalid column name 'SP_NEXTUSER'.

    How can I do this?

     

     

  • June 30, 2005 at 1:08 pm

    #570084

    DECLARE @RETURNVAL AS smallint

    EXEC @RETURNVAL = dbo.SP_NEXTUSER

  • June 30, 2005 at 1:41 pm

    #570108

    Or

    CREATE PROCEDURE SP_NEXTUSER @NEXTUSERNUM smallint OUTPUT

    AS

    --ME REGRESA EL SIGUIENTE NUMERO MAS PEQUEÑO DESOCUPADO PARA UN USER_NUMBER

    -- EJEMPLO SI TENGO DADOS DE ALTA 1,2,7,21... ME REGRESARIA UN 3

    SET NOCOUNT ON

    select @NEXTUSERNUM= min(num) from

    (SELECT USER_NUM+1 as num

    FROM tbUSERS ) tb

    left outer join tbUSERS on tb.num=tbUSERS.user_num

    WHERE tbUSERS.USER_NUM IS NULL

    RETURN

    GO

    DECLARE @RETURNVAL AS smallint

    EXEC dbo.SP_NEXTUSER @RETURNVAL OUTPUT

  • June 30, 2005 at 1:41 pm

    #570107

    Thanks a lot!!!

  • June 30, 2005 at 1:43 pm

    #570112

    Yup... the advantage of the output parameter is that you can have as many as you want and that you're not stuck with the int datatype. The can also be used as intput parameters if needed.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply