0

I am trying to insert data into database using stored procedure via ExecuteSqlRawAsync using the below code.

var param = new SqlParameter[] {
                        new SqlParameter() {
                            ParameterName = "@customerId",
                            SqlDbType =  SqlDbType.Int,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = obInfo.customerId
                        },
                        new SqlParameter() {
                            ParameterName = "@customerName",
                            SqlDbType =  SqlDbType.VarChar,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = obInfo.customerName
                        },
                        new SqlParameter() {
                            ParameterName = "@customerPhone",
                            SqlDbType =  SqlDbType.VarChar,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = obInfo.customerPhone
                        },
                        new SqlParameter() {
                            ParameterName = "@customerAddress",
                            SqlDbType =  SqlDbType.VarChar,
                            Size = 1000,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = obInfo.customerAddress
                        }

                };

await _db.Database.ExecuteSqlRawAsync("exec sp_InsertData @customerId, @customerName, @customerPhone, @customerAddress", param);

My stored procedure has few more parameters having default value but does not need to add in the above code, ExecuteSqlRawAsync execute the stored procedure with parameter order, Is there any other way to execute stored procedure, so it will execute as per given parameters, the rest of parameters should have the default value.

My Stored procedure look like this

Create proc sp_InsertData 
@customerId INT,
@customerName VARCHAR(100)=NULL,
@customerPhone VARCHAR(50)=NULL,
@refName VARCHAR(50)=NULL,
@refPhone VARCHAR(50)=NULL,
@customerAddress VARCHAR(500)=NULL,
@userId INT=0,
@changeIP VARCHAR(20)=NULL
AS
BGEIN

    IF @customerId=0
    BEGIN
      INSERT INTO tbl_CustomerInfo(customerName, customerPhone, customerAddress)
      VALUES(@customerName, @customerPhone, @customerAddress)
    END
    ELSE
    BEGIN
       
      ///SOME STATEMENT HERE LIKE CALLING FROM SOME OTHER ROUTINE....
    
    END


END

Thanks in advance...

  • FYI the `sp_` prefix is reserved by Microsoft, and is [documented](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15#procedure_name) to **not** be used for user objects. Doing so comes with a performance penalty and could result in your procedure simply not working in the future. – Thom A Oct 24 '21 at 14:21
  • 1
    Is this not working?? – ErikEJ Oct 24 '21 at 14:25
  • Either specify each parameter `@customerId = @customerId` etc, or create a proper ADO.Net `SqlCommand` with `CommandType.StoredProcedure` (and then you just pass the name of the procedure, nothing more) – Charlieface Oct 24 '21 at 15:17
  • Hi Chrlieface, Thank you for your response, can you please send me more good example? like you mean await _db.Database.ExecuteSqlRawAsync("exec sp_InsertData @customerId={param[0]}, @customerName={param[1]} .....); etc? – Muhammad Faheem Chughtai Oct 25 '21 at 05:09

0 Answers0