Stored Procedure Default Value

Question

I'm a newbie when it comes to SQL. When creating a stored procedure with parameters as such:

@executed   bit,
@failure   bit,
@success   bit,
@testID    int,
    @time      float = 0,
@name    varchar(200) = '',
@description  varchar(200) = '',
@executionDateTime  nvarchar(max) = '',
@message   nvarchar(max) = ''

This is the correct form for default values in T-SQL? I have tried to use NULL instead of ''.

When I attempted to execute this procedure through C# I get an error referring to the fact that description is expected but not provided. When calling it like this:

        cmd.Parameters["@description"].Value = result.Description;

result.Description is null. Should this not default to NULL (well '' in my case right now) in SQL?

Here's the calling command:

        cmd.CommandText = "EXEC [dbo].insert_test_result @executed,
                           @failure, @success, @testID, @time, @name, 
                           @description, @executionDateTime, @message;";

        ...
        cmd.Parameters.Add("@description", SqlDbType.VarChar);
        cmd.Parameters.Add("@executionDateTime", SqlDbType.VarChar);
        cmd.Parameters.Add("@message", SqlDbType.VarChar);

        cmd.Parameters["@name"].Value = result.Name;
        cmd.Parameters["@description"].Value = result.Description;
        ...

        try
        {
            connection.Open();
            cmd.ExecuteNonQuery();
        }
        ...
        finally
        {
            connection.Close();
        }

Answer 1

A better approach would be to change the CommandText to just the name of the SP, and the CommandType to StoredProcedure - then the parameters will work much more cleanly:

cmd.CommandText = "insert_test_result";
cmd.CommandType = CommandType.StoredProcedure;

This also allows simpler passing by name, rather than position.

In general, ADO.NET wants DBNull.Value, not null. I just use a handy method that loops over my args and replaces any nulls with DBNull.Value - as simple as (wrapped):

    foreach (IDataParameter param in command.Parameters)
    {
        if (param.Value == null) param.Value = DBNull.Value;
    }

However! Specifying a value with null is different to letting it assume the default value. If you want it to use the default, don't include the parameter in the command.

Answer 2

If you aren't using named parameters, MSSQL takes the parameters in the order received (by index). I think there's an option for this on the cmd object.

so your SQL should be more like

EXEC [dbo].insert_test_result 
@executed = @executed,
@failure = @failure, 
@success = @success, 
@testID = @testID, 
@time = @time, 
@name = @name, 
@description = @description, 
@executionDateTime = @executionDateTime, 
@message = @message;

Answer 3

cmd.CommandText = "insert_test_result";
cmd.Parameters.Add(new SQLParameter("@description", result.Description));
cmd.Parameters.Add(new SQLParameter("@message", result.Message));
try
{
     connection.Open();
     cmd.ExecuteNonQuery();
}
finally
{
     connection.Close();
}