Stored procedure passing parameter

Question

If I execute a stored procedure with passing parameter it's executing fine. If I didn't pass a parameter, sometimes it is giving an error. null dataset.

Same sp if I pass with dummy parameter it is returning dataset.

Somebody help me please.

Priya

Answer 1

If the stored procedure declares a parameter that is not optional, you will always need to pass a value to it. If you wish to pass a null value, you should assign DBNull.Value to the parameter before executing it from .NET code.

A parameter is optional if it is given a default value in the declaration, like this:

CREATE PROCEDURE [dbo].[spName]
(
    @ParamName varchar(50) = NULL
)
AS
BEGIN
  -- procedure code goes here
END

Answer 2

hi thank you for the reply, but my question is if create a stored procedure with passing parameter and called it in subsonic means its working fine.

CREATE PROCEDURE [dbo].spName = NULL)ASBEGIN -- procedure code goes hereEND

if i create a stored procedure without passing parameter

CREATE PROCEDURE [dbo].[spName] AS BEGIN -- procedure code goes here END

if i try to call this procedure in subsonic it is giving object referance not set to an instance error.