June 30, 2005 at 1:06 pm
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
DECLARE @RETURNVAL AS smallint
EXEC @RETURNVAL = dbo.SP_NEXTUSER
June 30, 2005 at 1:41 pm
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
Thanks a lot!!!
June 30, 2005 at 1:43 pm
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