C# stored procedure without passing optional parameters

Question

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.

Then ask the author of SqlHelper, whatever that is, to allow it in whatever code they're using to call the stored procedure. — Jeroen Mostert
– Jeroen Mostert, Commented Jul 1, 2016 at 11:01

Rahul Tripathi · Accepted Answer · 2016-07-01 05:49:02Z

2

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.

answered Jul 1, 2016 at 5:49

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2016-10-01 07:53:19Z

-1

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 
)

edited Oct 1, 2016 at 7:53

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 1, 2016 at 6:57

taotechnocom

2281 silver badge8 bronze badges

Collectives™ on Stack Overflow

C# stored procedure without passing optional parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related