Adding new parameter to stored procedure

Question

I have a stored procedure in my database that currently takes in and utilizes 11 parameters (all working great). I need to add a new parameter to this for a new column I added.

We always explicitly define our columns in code, so there was no issue adding a column to the end of the table. However, if I add a new parameter in my stored procedure to populate this new column, will it throw an error back to my C# code if it isn't supplied, or will it default to null (or some other value) for the parameter?

Example C# code to call the stored procedure:

public static void InsertMailLog(string messageId, DateTime sentOrReceivedDate,
        string fromAddress, string toAddress, string subject, string receivedMessage, string tailNumber,
        string messageType, string direction, string sentOrReceived, string distributionList, ILogger AppEventLog, string filename = null)
{
        List<string> lstParameterValues = new List<string>();

        try
        {
            lstParameterValues.Add(messageId ?? "");
            lstParameterValues.Add(sentOrReceivedDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            lstParameterValues.Add(fromAddress ?? "");
            lstParameterValues.Add(toAddress);
            lstParameterValues.Add(subject ?? "");
            lstParameterValues.Add(receivedMessage ?? "");
            lstParameterValues.Add(tailNumber ?? "");
            lstParameterValues.Add(messageType ?? "");
            lstParameterValues.Add(direction ?? "");
            lstParameterValues.Add(sentOrReceived ?? "");
            lstParameterValues.Add(distributionList ?? "");
            lstParameterValues.Add(filename ?? "");  //THIS IS NEW, but it has not been published yet as the SP hasn't been updated.

            CommonDAL.ExecSpNonQuery("spMailLogInsert", lstParameterValues);
        }
        catch (Exception ex)
        {
            CommonBLL.LogError(ex, MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, "Error", messageId, tailNumber, messageType, "", Settings.Default.ContentProvider, AppEventLog);
        }
    }

Example stored procedure code:

ALTER PROCEDURE [dbo].[spMailLogInsert]
    @SdMessageId         varchar(50),
    @SentOrReceivedDate  datetime,
    @FromAddress         varchar(100),
    @ToAddress           varchar(100),
    @Subject             varchar(255),
    @Message             varchar(MAX),
    @TailNumber          varchar(50),   
    @MessageType         varchar(50),
    @Direction           varchar(50),
    @SentOrReceived      varchar(50),
    @DistributionList    varchar(50),
    @Filename            varchar(50)  --THIS IS NEW
AS
    SET NOCOUNT ON

    INSERT MailLog (SdMessageId, SentOrReceivedDate, FromAddress, ToAddress,
                    [Subject], [Message], TailNumber, MessageType, Direction,
                    SentOrReceived, DistributionList, Filename  --THIS IS NEW
                   ) 
    VALUES (@SdMessageId, @SentOrReceivedDate, @FromAddress, @ToAddress,
            @Subject, @Message, @TailNumber, @MessageType,
            @Direction, @SentOrReceived, @DistributionList,
            @Filename  --THIS IS NEW
           )

I completely understand that this is a terrible use of a stored procedure. I should be using Entity Framework, but it's already written, and I have a project to update the entire project to use EF in the DAL at a later date (this is very old code).

My question is, if I add the new parameter @Filename" to the stored procedure before the new C# code above gets published, will I get an error, or will the stored procedure parameter simply default to NULL? Or, if someone has a better way to default this to NULL or empty string, if it isn't supplied, I'm all ears.

Answer 1

Either Make it nullable like this.

    ALTER PROCEDURE [dbo].[spMailLogInsert]
    @SdMessageId         varchar(50),
    @SentOrReceivedDate  datetime,
    @FromAddress         varchar(100),
    @ToAddress           varchar(100),
    @Subject             varchar(255),
    @Message             varchar(MAX),
    @TailNumber          varchar(50),   
    @MessageType         varchar(50),
    @Direction           varchar(50),
    @SentOrReceived      varchar(50),
    @DistributionList    varchar(50),
    @Filename            varchar(50) = NULL --THIS IS NEW
......

Or Add a Default value like this:

    ALTER PROCEDURE [dbo].[spMailLogInsert]
    @SdMessageId         varchar(50),
    @SentOrReceivedDate  datetime,
    @FromAddress         varchar(100),
    @ToAddress           varchar(100),
    @Subject             varchar(255),
    @Message             varchar(MAX),
    @TailNumber          varchar(50),   
    @MessageType         varchar(50),
    @Direction           varchar(50),
    @SentOrReceived      varchar(50),
    @DistributionList    varchar(50),
    @Filename            varchar(50) = 'abc.txt' --THIS IS NEW
......

Answer 2

You could go ahead with the SP and just use a default parameter. https://technet.microsoft.com/en-US/library/ms189330(v=SQL.105).aspx

@Filename            varchar(50) = NULL  --THIS IS NEW