Estimated/Actual Execution plan not displayed!!!

I have a Stored Procedure which was showing Execution Plan when execute it until some days back!!!

Suddenly, Execution plan not generating for the Procedure when I execute it!! But, Its executing and showing the result as expected.

I could not also see the Procedure definition and bellow is the Err!!

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 116
There is no text for object 'proc_sample2'.

What would be the reason ?

It seems like the Procedure's definition got !!! Encrypted !!!

Let's try to find out...

USE  <DatabaseName>
GO
SELECT CASE OBJECTPROPERTY([Object_ID],'IsEncrypted') WHEN 1 THEN 'YES' ELSE 'NO' END [IsEncrypted]
FROM sys.Objects WHERE type_desc ='SQL_STORED_PROCEDURE'
AND is_ms_shipped =0
AND Name='proc_sample2'
GO

Yes. That is correct!! The Procedure's definition got Encrypted. So that the Execution Plan can not be generated as per the MSDN Definition...

"Execution plans are not displayed for encrypted stored procedures or for triggers."

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