SQL SERVER STORED PROC OPTIONAL PARAMETER

I have studied and debugged the procedure and found that there was a recursive call to the same procedure where in the optional parameter was not passed during the recursive call thereby resetting its value to 0.

Thank u all for your support.

So, you should have a procedure declaration such as:

CREATE PROCEDURE foo
    @Param1 varchar(255), @Param2 varchar(255),
    @OptionalParam1 int = 0
AS
    --Do Stuff
GO

Now, in the body of the SP, are you overwriting @OptionalParam1 with something? Show us your SP code.

Or is it the code you're using to call the SP? How are you calling the SP? From within a T-SQL Script, or an application? Either way, show us the code!