x

SQL SERVER STORED PROC OPTIONAL PARAMETER

Hello all,

I am using a stored procedure to perform my logic which has 2 mandatory parameters and 1 optional parameter having default value as 0 (INT).

When i am calling the procedure, i also pass the optional parameter as 1.

On debugging the stored procedure i found that the optional parameter still takes value as 0 . I have compiled the procedure many times but no success.

Kindly help me why such random behaviour of SQL server.Also how can I solve this issue ??

Thanks Tushar
more ▼

asked Oct 15, 2012 at 07:07 PM in Default

tusharsnk gravatar image

tusharsnk
40 1 1 1

You are probably overwriting the value somewhere - can you post the full stored procedure script and we will be able to help you better.
Oct 15, 2012 at 07:11 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Oct 16, 2012 at 05:33 PM

tusharsnk gravatar image

tusharsnk
40 1 1 1

(comments are locked)
10|1200 characters needed characters left

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!
more ▼

answered Oct 15, 2012 at 07:49 PM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951

asked: Oct 15, 2012 at 07:07 PM

Seen: 739 times

Last Updated: Oct 16, 2012 at 05:33 PM