ASP.NET - Pass Null Value to StoredProc

Hi All,

How can I pass Null value to the storedProc, as the input paramter is Int value. while Passing it is throwing error Procedure or function 'prc_Test' expects parameter '@param', which was not supplied.

Making optional param in StoredProc is not the option, I need to pass from page.

Please help.

Thanks in advance.

Try the following SP

CREATE PROCEDURE TestProc
(
    @Param1 varchar(50) = NULL,
    @Param2 varchar(50) = NULL,
    @Param3 varchar(50) = NULL
)
AS
SELECT
    *

FROM
    TestTable

WHERE
    ((@Param1 IS NULL) OR (col1 = @Param1)) AND
    ((@Param2 IS NULL) OR (col2 = @Param2)) AND
    ((@Param3 IS NULL) OR (col3 = @Param3))

You can use Optional Parameter in Stored Procedure.
Check out this example;

create  proc  dbo.spGetEmployees   

  @employeeID int = null,   
  @firstName varchar(255) = null,   

  @lastName varchar(255) = null  
as  

select  *   from  dbo.employees   

where   (id = @employeeID or @employeeID is null)   and  (firstName = @firstName or @firstName is null)   

  and   (lastName = @lastName or @lastName is null)  

So it not compulsory to pass these parameters to SP.....

Using optional parameters can come in handy for a number of situations:

• When a parameter is missing, assign it a default value. You could do that through the parameter declaration itself (e.g. @MyNumber INT = 1), or you might use a complex algorithm within the procedure to select the default value.
• When a parameter is missing, return a specific error code that has special meaning to the calling procedure, so it can take appropriate action. My example procedure below demonstrates this approach.
• When a parameter is missing, branch to specific logic within the stored procedure.

http://www.nerdymusings.com/LPMArticle.asp?ID=37

As you said null parameter in sp in not you want right,so tell me what is your requirement,do you want to pass it from the page ?

let me know
thxs