SQL STORED PROCEDURE INSERTING CONVERT ERROR

Question

I am getting error when i try insert with stored procedure

What i have try:

SQL

CREATE procedure [dbo].[urunGiris]
@TableName varchar(100),
@MalzemeStokNo varchar(50),
@MalzemeAd varchar(100),
@Irsaliye varchar(100),
@MalzemeAgirlik varchar(50),
@GirenMiktar int,
@GirenTonaj FLOAT (53),
@CikanMiktar int,
@CikanTonaj FLOAT (53),
@KalanMiktar int,
@KalanTonaj FLOAT (53),
@Tarih datetime
As
Begin
     Declare @sql nvarchar(max)
     Set @sql = 'insert into ' + @TableName +' (MalzemeStokNo, MalzemeAd, Irsaliye, MalzemeAgirlik,GirenMiktar,GirenTonaj,CikanMiktar,CikanTonaj,KalanMiktar,KalanTonaj,Tarih)
     values(''' + @MalzemeStokNo + ''',''' + @MalzemeAd + ''',''' + @Irsaliye + ''',''' + @MalzemeAgirlik + ''',' + @GirenMiktar + ',
     ''' + @GirenTonaj + ''',''' + @CikanMiktar + ''',''' + @CikanTonaj + ''',''' + @KalanMiktar + ''',''' + @KalanTonaj + ''',''' + @Tarih + ''')'
 Execute sp_executesql @sql
End

c#

                    conn.Open();
                    SqlCommand cmd = new SqlCommand("urunGiris", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("TableName", SqlDbType.VarChar, 100).Value = str;
                    cmd.Parameters.Add("MalzemeStokNo", SqlDbType.VarChar, 50).Value = stokNo.Text;
                    cmd.Parameters.Add("MalzemeAd", SqlDbType.VarChar, 100).Value = malzemeAdi.Text;
                    cmd.Parameters.Add("Irsaliye", SqlDbType.VarChar, 100).Value = irsaliye.Text;
                    cmd.Parameters.Add("MalzemeAgirlik", SqlDbType.VarChar, 50).Value = agirlik;
                    cmd.Parameters.Add("GirenMiktar", SqlDbType.Int).Value = int.Parse(girenMiktar.Text);
                    cmd.Parameters.Add("GirenTonaj", SqlDbType.Float).Value = double.Parse(String.Format("{0:0.00}", double.Parse(malzemeAgirlik.Text.Replace(".", ",")) * double.Parse(girenMiktar.Text.Replace(".", ","))));
                    cmd.Parameters.Add("CikanMiktar", SqlDbType.Int).Value = ckn;
                    cmd.Parameters.Add("CikanTonaj", SqlDbType.Float).Value = ckn2;
                    cmd.Parameters.Add("KalanMiktar", SqlDbType.Int).Value = int.Parse(girenMiktar.Text);
                    cmd.Parameters.Add("KalanTonaj", SqlDbType.Float).Value = double.Parse(String.Format("{0:0.00}", double.Parse(malzemeAgirlik.Text.Replace(".", ",")) * double.Parse(girenMiktar.Text.Replace(".", ","))));
                    cmd.Parameters.Add("Tarih", SqlDbType.DateTime, 100).Value = DateTime.Now;
                  
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Ürün Kaydedildi ", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    conn.Close();

Error:

Answer 1

Because i have many tables and i need to insert with selected table

You mean that you have many tables with exactly the same columns? That sounds like a design error to me. Most likely, all those tables should be a single table.

Answer 2

Why you're doing dynamic SQL at all? Why you're not writing normal SQL and use the actual table to insert your data?

Answer 3

Why do you use dynamic SQL here, I don't see any reason for it? Dynamic SQL often causes issues.

@GirenMiktar int, ... ''',' + @GirenMiktar + ',

You can not "add" numeric values (here int) to a string, that causes the error. You have to convert it to varchar first =>

.. ''',' + CONVERT(varchar(10), @GirenMiktar) + ',

Answer 4

Because i have many tables and i need to insert with selected table

Answer 5

Also be careful of varchar parameters as they allow sql injection. You should build a function to make them safe. Or insert the parameters into a temp table via non dynamic sql, then use dynamic insert from the temp.