Cannot use the OUTPUT option when passing a constant to a stored procedure.

When we use Output parameter in a Stored procedure, There is a chance to get the following Error... Why?, and How to solve it ?

Msg 179, Level 15, State 1, Line 3

Cannot use the OUTPUT option when passing a constant to a stored procedure.

/*Creating a stored procedure*/

If OBJECT_ID('Usp_Procedure1') Is Not Null

Drop Proc Usp_Procedure1

Go

Create Proc Usp_Procedure1
(

@Param1 Varchar(50) Output

)As

Begin

Set Nocount On

Select @Param1 = 'SQL Server Administration'

End

Go

/*Executing the stored procedure*/

Exec Usp_Procedure1 'SQL Server BI' Output

Go
 
The following error occurred...

Msg 179, Level 15, State 1, Line 2

Cannot use the OUTPUT option when passing a constant to a stored procedure.

Why?

- The reason is, we have one Output parameter in a stored procedure(Usp_Procedure1), Normally, Output parameters can be used in both directions (Input and Output).

- So, when we access the stored procedure, Output keyword is required only to return the data through the Output parameters. If we don't want to return any data through the Output parameters then, The Output keyword should not be used.

If we want to return any data through the Output parameter:

Declare @P1 Varchar(50)

Exec Usp_Procedure1 @P1 Output

Select @P1

Go

- Output keywork should be used along with parameter @P1

 

If we don't want to return any data through the Output parameter:
Exec Usp_Procedure1 'SQL Server BI'

Go

- Output parameter should not be used when passing constant value instead of variable.

Conclusion:

1. Output keyword should not be used when we pass the constant value to the stored procedure directly instead of a variable.

Exec Usp_Procedure1 'SQL Server BI'

Go

2. Output keyword should be used when the value should be returned from the stored procedure through the variable.

Declare @P1 Varchar(50)

Exec Usp_Procedure1 @P1 Output

Select @P1

Go