Using 0 in stored procedure

[stevet26]

Hi

I have an if clause in my code to add the final parameter value to send to the database.

If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID"))
End If

Now if the user is in the role, the error is triggered saying that @C_ID is expected by the stored procedure. If i then change the value from 0 to 10, the stored procedure works fine.

Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?

Thanking you in advance.

 

[stsuing]

I think maybe SQL Server is interpreting 0 as the parameter not being added.

A workaround...

Delcare that parameter in the SP Like this
@C_ID int = 0 --this is a default value therefore this parameter is optional

You don't have to even add the parameter to the command object in code.

 

[stevet26]

Thanks very much. I never even knew you could do that!