CODEWITHSUNDEEP
c#sql-serverstored-proceduresoptional-parameters
Emma Thapa
Emma Thapa

Reputation: 777

C# stored procedure without passing optional parameters

My stored procedure in SQL Server looks like this: 

ALTER PROC [dbo].[Rd_CreateModifyAssignmentType]
(
    @AssignmentTypeId nvarchar(50),
    @AssignmentTypeName nvarchar(50),
    @mode int,
    @Langtype nvarchar(10)='' 
)

While calling it from C# like this:

SqlHelper.ExecuteNonQuery("Rd_CreateModifyAssignmentType", AssignmentTypeId, AssignmentTypeName, mode);

it throws an exception:

Parameter count does not match Parameter Value count.

I want to call the procedure in C# without passing optional parameters.

Please help me with this.

Upvotes: 1

Views: 1428

Answers (2)

taotechnocom
taotechnocom

Reputation: 228

Please set default value by null all parameter in stored procedure:

ALTER PROC [dbo].[Rd_CreateModifyAssignmentType]
(
   @AssignmentTypeId nvarchar(50) = null,
   @AssignmentTypeName nvarchar(50) = null,
   @mode int = null,
   @Langtype nvarchar(10) = null 
)

Upvotes: -1

Rahul Tripathi
Rahul Tripathi

Reputation: 172618

In your code you can write as:

if (Langtype.HasValue)
   cmd.Parameters.AddWithValue("@Langtype", Langtype.Value);

So now what will happen is that your procedure will check for the value of optional parameter. If it will not find any value then the method is not going to add the @Langtype parameter into the command and it will use the default value as '' which you have specified in your database.

Upvotes: 2

Related Questions