crowcoder

using System;
using System.Collections;
using System.Collections.Generic;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {

crowcoder

static void Main(string[] args)
{
    SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
    bldr.DataSource = "(localdb)\\DEV";
    bldr.InitialCatalog = "DbParams";
    bldr.ApplicationName = "ParameterBehavior";
    bldr.IntegratedSecurity = true;

    using (SqlConnection con = new SqlConnection(bldr.ConnectionString))
    using (SqlCommand cmd = con.CreateCommand())

crowcoder

private static void InputWithNoDefault(SqlCommand cmd)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "usp_Input_No_Default";

    SqlParameter par = new SqlParameter("@param1", SqlDbType.VarChar);
    par.Direction = ParameterDirection.Input;
    par.Value = "Foo";
    cmd.Parameters.Add(par);
}

crowcoder

CREATE PROCEDURE [dbo].[usp_Input_No_Default]
	@param1 varchar(50)
AS
	SELECT @param1;	
RETURN 0;

crowcoder

private static void InputWithNoDefault_NullValue(SqlCommand cmd)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "usp_Input_No_Default";

    SqlParameter par = new SqlParameter("@param1", SqlDbType.VarChar);
    par.Direction = ParameterDirection.Input;
    par.Value = null;
    cmd.Parameters.Add(par);
}

crowcoder

private static void InputWithNoDefault_DBNullValue(SqlCommand cmd)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "usp_Input_No_Default";

    SqlParameter par = new SqlParameter("@param1", SqlDbType.VarChar);
    par.Direction = ParameterDirection.Input;
    par.Value = DBNull.Value;
    cmd.Parameters.Add(par);
}

crowcoder

CREATE PROCEDURE [dbo].[usp_Input_Has_Default]
	@param1 varchar(50) = 'I''m a default value'
AS
	SELECT @param1;	
RETURN 0;

crowcoder

private static void InputNullWithDefault(SqlCommand cmd)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "usp_Input_Has_Default";

    SqlParameter par = new SqlParameter("@param1", SqlDbType.VarChar);
    par.Direction = ParameterDirection.Input;
    par.Value = null;
    cmd.Parameters.Add(par);
}

crowcoder

private static void InputDbNullWithDefault(SqlCommand cmd)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "usp_Input_Has_Default";

    SqlParameter par = new SqlParameter("@param1", SqlDbType.VarChar);
    par.Direction = ParameterDirection.Input;
    par.Value = DBNull.Value;
    cmd.Parameters.Add(par);
}

crowcoder

private static void NoInputWithDefault(SqlCommand cmd)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "usp_Input_Has_Default";
}