Why is my SQL CLR stored proc call claiming I am not providing a parameter I am?

Question

In a CLR procedure I have the following method:

private static void EndOwnershipForTeam(long assetId, int teamId)
{
    const string storedProcedureName = @"up_RemoveAssetOwnershipFromTeam";

    using (var connection = new SqlConnection("context connection=true"))
    using (var command = new SqlCommand(storedProcedureName, connection))
    {
        command.Parameters.AddWithValue("assetId", assetId);
        command.Parameters.AddWithValue("teamId", teamId);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

When I run this method is called I am getting the following error:

Msg 6522, Level 16, State 1, Procedure cp_RemoveAsset, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "cp_RemoveAsset":

System.Data.SqlClient.SqlException: Procedure or function 'up_RemoveAssetOwnershipFromTeam' expects parameter '@assetId', which was not supplied.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at StoredProcedures.EndOwnershipForTeam(Int64 assetId, Int32 teamId)

at StoredProcedures.cp_RemoveAsset(SqlInt32 userId, SqlString xaid)

Since my code is supplying the parameters (verified by displaying output via SqlContext.Pipe.Send() calls) why is it claiming that I"m not supplying a parameter I actually am?

Does your proc call any other procedures? If so, I would check that you're supplying the params to those correctly. — Siyual, Apr 09 '14 at 17:55
Specify command type: `command.CommandType = CommandType.StoredProcedure;` — Habib, Apr 09 '14 at 17:58

score 3 · Answer 1 · answered Apr 09 '14 at 17:54

3

You left out the @ symbols in your lines

command.Parameters.AddWithValue("assetId", assetId);
command.Parameters.AddWithValue("teamId", teamId);

They should be

command.Parameters.AddWithValue("@assetId", assetId);
command.Parameters.AddWithValue("@teamId", teamId);

answered Apr 09 '14 at 17:54

Golden Dragon

511
5
13

3

The @ is not required – Siyual Apr 09 '14 at 17:55
@Siyual In some cases it is required, although I'm not sure if this is one of them. – Joachim Isaksson Apr 09 '14 at 17:56
Some methods may be permissive, but the MSDN documentation for `SqlParameter.ParameterName` indicates that the @ is required, so the safest bet is to always use it. – Sean U Apr 09 '14 at 17:59
I'd think in this case it is required. Were assetId a reference type instead of a `long` that could cause the same error if it were `null`, but that is not the case here. – Golden Dragon Apr 09 '14 at 18:00
In stored proc calls the @ is not required. – KallDrexx Apr 09 '14 at 18:06
@KallDrexx They are if you don't set the command type to be a stored procedure. Best practice is to always include the `@` – Rowland Shaw Apr 09 '14 at 18:20
I'm pretty sure if you specify it as a stored procedure and include the @ it doesn't work. I remember having issues like that before. Too bad it's so confusing when/if you need them :-/ – KallDrexx Apr 09 '14 at 19:49

score 3 · Accepted Answer · answered Apr 09 '14 at 18:04

Looks like as written, your code is instructing SQL Server to just try executing this:

up_RemoveAssetOwnershipFromTeam

In other words, just the procedure with no parameters supplied.

To wire up the parameters you need to either specify CommandType.StoredProcedure, or wire up the command parameters explicitly:

// option 1
private static void EndOwnershipForTeam(long assetId, int teamId)
{
    const string storedProcedureName = @"up_RemoveAssetOwnershipFromTeam";

    using (var connection = new SqlConnection("context connection=true"))
    using (var command = new SqlCommand(storedProcedureName, connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("assetId", assetId);
        command.Parameters.AddWithValue("teamId", teamId);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

// option 2
private static void EndOwnershipForTeam(long assetId, int teamId)
{
    const string sql = @"exec up_RemoveAssetOwnershipFromTeam @assetId, @teamId";

    using (var connection = new SqlConnection("context connection=true"))
    using (var command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@assetId", assetId);
        command.Parameters.AddWithValue("@teamId", teamId);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

Yep that was the issue. I had forgotten to set the Command Type to stored procedure! — KallDrexx, Apr 09 '14 at 18:06
good catch. Though the parameters supplied would be used if the command text formed a *parameterized query* and matched on of the query parameters. — Nicholas Carey, Apr 09 '14 at 18:07

score 0 · Answer 3 · answered Apr 09 '14 at 18:05

0

You've omitted the '@' prefix.
What is the default collation configured for your sql server and/or database? If it is case-sensitive, you'll need to match case exactly.

answered Apr 09 '14 at 18:05

Nicholas Carey

71,308
16
93
135

Why is my SQL CLR stored proc call claiming I am not providing a parameter I am?

3 Answers3