SQL Server stored procedure expects parameter: error while calling from code

Question

I have written this stored procedure that accepts the clientId as parameter. As you can see in the SQL Server trace, I have passed the client id while calling from .NET, but still its showing the error that clientId parameter is not supplied. What am I doing wrong ?

Stored procedure:

SQL Server trace when calling from .NET:

I'm still getting the error:

Procedure or function 'USP_LoadPerformaceTemplate' expects parameter '@clientId', which was not supplied.

Does that error show up in your trace? It should - so perhaps you are not looking at the source of the problem. The most likely explanation is a fault in your application code - which you should post. And please avoid using images - something a person with your rep should know. — SMor
– SMor, Commented Jun 9, 2021 at 18:04
The call to the SP is missing the parameter. The call to sp_executesql passes it, but that statement it runs does nothing with said parameter; it just runs the SQL dbo.USP_LoadPerformaceTemplate. we need the application code here. — Thom A
– Thom A ♦, Commented Jun 9, 2021 at 18:07

Charlieface · Accepted Answer · 2021-06-09 20:39:27Z

1

In your C# code (which you haven't shown us), you have not set CommandType

command.CommandType = CommandType.StoredProcedure;
// or
using (var command = new ... { CommandType = CommandType.StoredProcedure})

So what is happening is that the SQL is being interpreted as a direct query batch:

[dbo].[USP_LoadPerformaceTemplate]

The word EXEC is not needed. So it is trying to execute it without the parameter.

But when CommandType.StoredProcedure is specified, it is sent as an RPC command to call the stored procedure.

answered Jun 9, 2021 at 20:39

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

SQL Server stored procedure expects parameter: error while calling from code

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related