SQL Server procedure, why two input parameters

Question

I have this procedure, it asking me 2 input parameters, don't know why as I'm declaring the 2nd parameter (@monto) as an output:

ALTER PROCEDURE [dbo].[SP_MONTO_APROBADO]  
   @ID INT,  
   @MONTO INT OUTPUT  
AS  
BEGIN  
   SELECT @MONTO = MONTO_APROBADO   
   FROM LICITACION 
   WHERE COD_PROYECTO = @ID  
END

Any hint please?

Because you need to tell whatever it is your using where to store the value. (not sure where that dialogue is from). — Thom A
– Thom A ♦, Commented Mar 9, 2018 at 14:57
@Larnu please, could you tell me how, I'm very new to sqlserver (just 1 hour...) — pmiranda
– pmiranda, Commented Mar 9, 2018 at 14:59
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Mar 9, 2018 at 15:49

Damien_The_Unbeliever · Accepted Answer · 2018-03-09 14:59:10Z

1

Just because a parameter is marked as output, that doesn't mean that it cannot be supplied a value as input which will be readable within the stored procedure.

That is, T-SQL doesn't have a concept like inout because all output parameters are effectively this.

If you don't want to supply a value, make sure the parameter can accept nulls and then supply that as the input value.

answered Mar 9, 2018 at 14:59

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pmiranda Over a year ago

Thanks. In that case, how could I declare it as null?

Jesús López · Accepted Answer · 2018-03-09 15:00:47Z

1

Because OUTPUT parameters are actually input-output parameters

answered Mar 9, 2018 at 15:00

Jesús López

9,3489 gold badges47 silver badges83 bronze badges

Comments

Thom A · Accepted Answer · 2018-03-09 15:05:46Z

When using a Stored procedure with an OUTPUT parameter, you still need to specify it when you execute the procedure.

I don't know what that image is from, however, using SQL you would need to do:

DECLARE @ID int, @Mondo int;
SET @ID = 1; --guessed value

EXEC SP_MONTO_APROBADO @ID, @Mondo OUTPUT;

PRINT @Mondo; --Don't know what you're doing with the OUTPUT value

Not specifying the second second parameter would result in a syntax error; as you aren't providing enough parameters for the SP. Regardless of if a Parameter in an input or output you still need to provide them at execution so that the Data engine knows where to get the values from, or where to store the resulting ones.

Also, it's work noting that prefixing your objects like that is a bad idea. Is the sp_ prefix still a no-no?

Collectives™ on Stack Overflow

SQL Server procedure, why two input parameters

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related