mercredi 26 août 2015

Adding new parameter to stored procedure

I've done some Googling, but I can't seem to find an answer to what I'm looking for. Maybe my search terms are off. Here is my situation:

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 SP:

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 SP:

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 SP 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.

Aucun commentaire:

Enregistrer un commentaire