CODEWITHSUNDEEP
c#sqlasp.netsql-serverado.net
user3518032
user3518032

Reputation: 423

Passing values to a stored procedure

I have a stored procedure in SQL Server which has many fields and some of them are initialized in parameters list. So do I need to pass a value for those parameters from asp.net or bypass them? See my code below to get my meaning.

Stored procedure:

ALTER PROCEDURE [dbo].[ADD_SMS_InQueue]
(
   @ToMobNo varchar(15),
   @Message nvarchar(2000),
   @encoding_Type int = 0,
   @IsAlertSMS bit = 0,
   @priority int = 2,
   @SendByUser int = 1,
   @MessageType tinyint = 0
)
AS
   BEGIN TRY
   BEGIN TRANSACTION
       INSERT INTO MessageQueue ([QueueDateTime], Client_ID, ToMobileNo, 
            [Message], LongMessageType_ID, EncodingType_ID, AlertMessage, LocalSMS, Priority_ID, Attempts, TotalAttempts, Status_ID, SendByUser, MessageType)
       VALUES (GETDATE(), 1, @ToMobNo, @Message, 3, @encoding_Type, @IsAlertSMS, 1, @priority, 0, 0, 1, @SendByUser, @MessageType)

       COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
        ROLLBACK TRANSACTION
   END CATCH

C# code for sending vales:

public class MessageQueueBizz
{
        public string ToMobileNo { get; set; }
        public string Message { get; set; }

        public MessageQueueBizz(string ToMobileNo, string Message) 
        {
            this.ToMobileNo = ToMobileNo;
            this.Message = Message;
        }
    }
    public class ManageQueueBizz 
    {
        public int Insert(MessageQueueBizz MessageQueueBizz)
        {
            SqlCommand cmd = new SqlCommand("ADD_SMS_InQueue", DataBaseConnectionSMSDB.OpenConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter pToMobileNo = new SqlParameter("@ToMobNo", MessageQueueBizz.ToMobileNo);
            cmd.Parameters.Add(pToMobileNo);
            SqlParameter pMessage = new SqlParameter("@Message", MessageQueueBizz.Message);
            cmd.Parameters.Add(pMessage);

            int result = Convert.ToInt32(cmd.ExecuteNonQuery());

            DataBaseConnectionSMSDB.CloseConnection();

            return result;
        }

Upvotes: 0

Views: 131

Answers (1)

BossRoss
BossRoss

Reputation: 869

You shouldn't need to include the parameters that have been initialized in the stored proc

Upvotes: 1

Related Questions