SqlCommand stored procedure without a named parameter

Question

I've written a query parser that should create a SqlCommand and execute a stored procedure. The query for the stored procedure can come in many forms, including this one:

exec dbo.sp_StoredProcedureName 1599800

In this case, I create the SqlParameter this way:

var param = new SqlParameter() { Value = paramValue };

I get an error stating that '@Parameter1' is not a parameter for procedure sp_StoredProcedureName.

Is there a way I can do this without running it as a standard query? I'd like to keep it consistent and build the SqlCommand as a StoredProcedure type if possible.

I was thinking maybe I could reflect the parameter names of the stored proc first, but wondering if there's another approach.

Just to confirm, the stored procedure does actually have a parameter in its definition, correct? — RBarryYoung, Oct 31 '12 at 16:56
Yes .. the SPs are named. And I can certainly try to enforce a parameter name requirement. but this is a migration and I'm trying to limit changes to existing behavior. — duckus, Oct 31 '12 at 17:55

Bridge · Answer 1 · 2012-10-31T17:08:13.717

Whilst there is a constructor for SQLParameter which doesn't set the name, you can't actually use a SQLParameter without setting the ParameterName property.

From MSDN (emphasis mine) :

The ParameterName is specified in the form @paramname. You must set ParameterName before executing a SqlCommand that relies on parameters.

Within your SP the parameter will have a name, look at the query's definition to find it.

If it's not known in advance what the name is going to be, try querying the sys.parameters table to find out what parameters a particular stored procedure takes.

It's better to call stored procedures specifying which parameter is which anyway (especially if there's more than one parameter):

exec dbo.sp_StoredProcedureName @myParam = 1599800

From C# you can add it by name once you know what your parameter is called:

cmd.Parameters.AddWithValue("@myParam", 1599800);

score 0 · Answer 2 · answered Oct 31 '12 at 17:22

When you create a command of the type you specified in the question, this command gets converted to a dynamic SQL query which is somewhat similar to the following snippet:

EXECUTE sp_executesql 'exec dbo.sp_storedprocName @FirstParam=value, ...'

Here, @FirstName is name of the parameter you specify while adding it to the SqlCommand object. This type of query cannot be created without specifying names of parameters.

You can view the query that gets generated by ADO.NET using SQL Server profiler. It is a good practice to open SQL Server profiler and see how a query is interpreted, as it helps in avoiding common mistakes we do while writing queries in ADO.NET

SqlCommand stored procedure without a named parameter

2 Answers2