C# / SQL Server : error when creating stored procedure from C# code-CodePudding

The below code is executed in C# Winforms. What I'm trying to do is creating tables and stored procedures when the program starts. I'm using IF NOT EXISTS in my SQL commands. If not exists, create, else do nothing.

Creating the tables is ok, it works beautifully.

But when creating stored procedures, Visual Studio throws this error;

Must declare the scalar variable @xxxx

Before getting this error I was using USE and GO commands and this approach also gave CREATE/ALTER PROCEDURE errors. As I researched on web, I gradually learnt that USE and GO is a no-no in C#. That's why I use Else instead of GO after IF NOT EXISTS.

The code works in T-SQL, but I guess I need to convert it to C# somehow.

Any help is appreciated. Thanks.

Update:

After I changed the StringBuilder to the just string, it fixed the previous error. Now it gives the below one. Couldn't find anything amiss though.

" System.Data.SqlClient.SqlException
    HResult=0x80131904
    Message=Incorrect Syntax near the 'Trendyol'.
  Unclosed quotation mark after the character string "', ')))))AS varchar) FROM urnBilgi INNER JOIN urn ON urn.stkID = bVeriID WHERE bVeriID = @stkID and bBilgiID = @bBilgiID AND(urn.fiyatS BETWEEN @fiyat_baslangic and @fiyat_bitis) END END'"

    public void createSP()
    {
        StringBuilder sbSP = new StringBuilder();

        sbSP.AppendLine(" IF NOT EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND object_id = object_id('etradeCore'))"  
                        " exec('CREATE PROCEDURE [dbo].[etradeCore]("   

                        " ELSE"  //Used Instead of GO
                     

                        " ALTER PROCEDURE [dbo].[etradeCore]("  
                        " @pazaryeri VARCHAR(50),"  
                        " @magaza VARCHAR(50),"  
                        " @stkID int,"  
                        " @komisyon decimal(9, 4),"  
                        " @fiyat_baslangic decimal(9, 4),"  
                        " @fiyat_bitis decimal(9, 4),"  
                        " @eklenecekfiyat decimal(9, 4)"  
                        ")"  
                        
                        " AS BEGIN"  

                        " SET NOCOUNT OFF"  
                        " SET ANSI_NULLS ON"  
                        " SET QUOTED_IDENTIFIER ON"  

                        " DECLARE @bBilgiID tinyint"  

                        " IF(@pazaryeri = 'Trendyol' and @magaza = 'Kozmeti')"  
                        " SET @bBilgiID = 58"  
                        " ELSE IF(@pazaryeri = 'Trendyol' and @magaza = 'Golden Rose')"  
                        " SET @bBilgiID = 52"  
                        " ELSE IF(@pazaryeri = 'Trendyol' and @magaza = 'Ziaja')"  
                        " SET @bBilgiID = 60"  
                        " ELSE IF(@pazaryeri = 'Hepsiburada' and @magaza = 'Kozmeti')"  
                        " SET @bBilgiID = 43"  
                        " ELSE IF(@pazaryeri = 'Hepsiburada' and @magaza = 'Golden Rose')"  
                        " SET @bBilgiID = 44"  
                        " ELSE IF(@pazaryeri = 'Hepsiburada' and @magaza = 'Ziaja')"  
                        " SET @bBilgiID = 43"  

                        " IF NOT EXISTS(select * from urnBilgi WHERE bVeriID = @stkID and bBilgiID = @bBilgiID and bDeger >= convert(varchar, 0))"  
                        " BEGIN"  
                        " INSERT INTO urnBilgi(bVeriID, bBilgiID, bDeger) VALUES(@stkID, @bBilgiID, '0')"  
                        " END"  
                        " ELSE"  
                        " BEGIN"  
                        " UPDATE urnBilgi SET bDeger ="  
                        " CAST(CONVERT(decimal(9, 4), ((((Select fiyatS from urn where stkID = @stkID)   @eklenecekfiyat)) *"  
                        " CONVERT(decimal(9, 4), convert(varchar, '1.'   REPLACE(@komisyon, '.', ''))) ))AS varchar)"  
                        " FROM urnBilgi"  
                        " INNER JOIN"  
                        " urn ON urn.stkID = bVeriID"  
                        " WHERE bVeriID = @stkID and bBilgiID = @bBilgiID AND(urn.fiyatS BETWEEN @fiyat_baslangic and @fiyat_bitis)"  
                        " END"  
                        " END" 
                        );
        using (SqlConnection connection = new SqlConnection(constring))
        {

            using (SqlCommand cmd = new SqlCommand(sbSP.ToString(), connection))
            {
                connection.Open();
                cmd.CommandType = CommandType.Text;
              
                    cmd.ExecuteNonQuery();
               
                connection.Close();
            }
        }
    }

CodePudding user response：

You cannot use GO in a SQL batch with SqlCommand. It is not valid T-SQL, it's just a batch separator used by sqlcmd and SSMS.

Instead you need to split batches by using a new command.

However in this particular instance, you don't actually need two commands. Firstly because you can combine them using CREATE OR ALTER PROCEDURE. Secondly even if you did need to, since you are just doing this via dynamic SQL you can just conditionally build a dynamic SQL script to pass through.

You can also remove the StringBuilder and use a verbatim @"" string instead. This allows you to insert newlines and make good use of whitespace.

Another issue with your current script is that you are not escaping ' for the dynamic SQL. Passing it in as a parameter as I have done avoids this, but if you want to do it all in one big batch you need to make sure to do so.

public void createSP()
{
    const string sqlExec = @"
IF NOT EXISTS (SELECT 1
    FROM sys.procedures
    WHERE name = 'etradeCore')
  exec(@sql);
";

    const string sbSP = @"
CREATE OR ALTER PROCEDURE [dbo].[etradeCore]
  @pazaryeri VARCHAR(50),
  @magaza VARCHAR(50),
  @stkID int,
  @komisyon decimal(9, 4),
  @fiyat_baslangic decimal(9, 4),
  @fiyat_bitis decimal(9, 4),
  @eklenecekfiyat decimal(9, 4)
AS

SET NOCOUNT OFF;

DECLARE @bBilgiID tinyint =
  CASE @pazaryeri
  WHEN 'Trendyol'
    THEN
      CASE @magaza
      WHEN 'Kozmeti'
        THEN 58
      WHEN 'Golden Rose'
        THEN 52
      WHEN 'Ziaja'
        THEN 60
      END
  WHEN 'Hepsiburada'
    THEN
      CASE @magaza
      WHEN 'Kozmeti'
        THEN 43
      WHEN 'Golden Rose'
        THEN 44
      WHEN 'Ziaja'
        THEN 43
      END
  END;

IF NOT EXISTS (SELECT 1
    FROM urnBilgi
    WHERE bVeriID = @stkID
      AND bBilgiID = @bBilgiID
      AND bDeger >= '0')
BEGIN
    INSERT INTO urnBilgi (bVeriID, bBilgiID, bDeger)
    VALUES (@stkID, @bBilgiID, '0');
END
ELSE
BEGIN
    UPDATE b
    SET bDeger = CAST(
      CONVERT(decimal(9, 4),
        (
          u.fiyatS   @eklenecekfiyat
        ) * CONVERT(decimal(9, 4), '1.'   REPLACE(@komisyon, '.', ''))
      ) AS varchar(30))
    FROM urnBilgi b
    INNER JOIN urn u ON u.stkID = b.bVeriID
    WHERE b.bVeriID = @stkID
      AND b.bBilgiID = @bBilgiID
      AND (u.fiyatS BETWEEN @fiyat_baslangic AND @fiyat_bitis);
END
"; 

    using (SqlConnection connection = new SqlConnection(constring))
    using (SqlCommand cmd = new SqlCommand(sqlExec, connection))
    {
        cmd.Parameters.Add("@sql", SqlDbType.NVarChar, -1).Value = sbSP;
        connection.Open();
        cmd.ExecuteNonQuery();
    }
}

Notes:

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON should be part of the connection string settings, not part of the batch
Use sys.procedures instead of sys.objects
Use EXISTS (SELECT 1 instead of EXISTS (SELECT *
It's unclear why you are passing @komisyon as a decimal only to remove the . perhaps you should pass an int instead, or perhaps some other way.
Always declare varchar with a length.
Why is bDeger a varchar anyway? Should it not be decimal?
Unclear why you need the inner SELECT fiyatS... subquery in the UPDATE and not just u.fiyatS.
CommandType.Text is the default.
There is no need to call Close() if you have a using block (as you should).
Why is SET NOCOUNT OFF? Normally it's better to leave it on.
This whole "upsert" procedure should probably be in a transaction, with HOLDLOCK hints, and should probably use @@ROWCOUNT to check.

CodePudding user response：

Caius Jard's comment is alright for now. Even though the real problem is not solved, I skipped the next step in my program.

You don't need to check the existence of your stored proc if you're running from c#; if the proc exists the attempt to create it will fail, and c# will experience an exception that you can ignore. Keep it simple