Optional/Null stored procedure parameters

[RollingMoose]

Here's what we're trying to do. We are using the command object to execute a SQL Server stored procedure that receives 21 input parameters. We collect this data from a form and in many cases the user will leave the text box empty. We need to send a null value in these instances as a parameter or have the parameter be optional. When sending a null value, an error is returned since the SQL Server parameter is a decimal or integer data type. Any ideas would be appreciated.

 

[SonOfEmidec1100]

By null value I assume you are sending an empty string - not NULL. Passing NULL will work, although the code in the sp will need to allow for it.

To make the parameter optional just supply a default value in the sp

CREATE PROCEDURE  dbo.test @number INT = 0 AS

select @number

GO