none
SqlExeption: Must declare the scalar variable "@theCategoryNbr" RRS feed

  • Question

  • Hello,

    I am using SQL Server Express and have created a Storage Procedure: "getallFeatureNbrsForSymbolandCategory"

    Now I have declared two parameters as seen there: @theSymbolNbr and @theCategoryNbr.

    But when I call this procedure from C#, I receive this error. I can't understand what could be missing?

    I get an error: 

    System.Data.SqlClient.SqlExeption (0x80131904): Must declare the scalar variable "@theCategoryNbr" at SqlConnection.OnError

    Storage Procedure: "getallFeatureNbrsForSymbolandCategory"

    CREATE PROCEDURE getallFeatureNbrsForSymbolandCategory
    	@theSymbolNbr SMALLINT,
    	@theCategoryNbr TINYINT
    
    AS
    
    BEGIN
    	DECLARE @FullQuery nvarchar(1000)
    	SET @FullQuery = N'SELECT _FeatureNbr FROM allFeaturesNumbersTable WHERE _FeatureCategory = @theCategoryNbr AND 
    																	         _SymbolNbr = @theSymbolNbr'
    
    
    	EXECUTE sp_executesql 
          @FullQuery,
          N'@theSymbolNbr SMALLINT', @theSymbolNbr,
          N'@theCategoryNbr TINYINT', @theCategoryNbr;
    END

    C# code to execute the Storage Procedure

            void function1()
            {
                SqlConnection connection = new SqlConnection(GetConnectionString());
    
                Int16 SymbolNbr = 0;
                byte CategoryNbr = 0;
                using (SqlCommand cmd = new SqlCommand("getallFeatureNbrsForSymbolandCategory", connection)) //1. create a command object identifying the stored procedure (gettheSymbolIndex)
                {
                    cmd.CommandType = CommandType.StoredProcedure; //2. set the command object so it knows to execute a stored procedure
                    cmd.Parameters.Add(new SqlParameter("@theSymbolNbr", SymbolNbr)); //(SMALLINT)
                    cmd.Parameters.Add(new SqlParameter("@theCategoryNbr", CategoryNbr)); //(TINYINT)
                    using (SqlDataReader rdr = cmd.ExecuteReader()) //execute the command
                    {
                        while (rdr.Read())  //iterate through results
                        {
                            //do something
                        }
                    }
                }
            }
            public String GetConnectionString() { return "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\andre\\Desktop\\!!!key-gen\\App123\\featuresDatabase1.mdf;Integrated Security=True;Connect Timeout=3600"; }
    Thank you!


    • Edited by Silvers11 Sunday, October 18, 2020 7:18 PM
    Sunday, October 18, 2020 7:16 PM

Answers

  • If you need dynamic queries, then try something like this:

    EXECUTE sp_executesql

          @FullQuery,

          N'@theSymbolNbr SMALLINT, @theCategoryNbr TINYINT',

          @theSymbolNbr = @theSymbolNbr,

          @theCategoryNbr = @theCategoryNbr

    • Marked as answer by Silvers11 Sunday, October 18, 2020 7:47 PM
    Sunday, October 18, 2020 7:35 PM

All replies

  • Does it work if you temporarily change the body of stored procedure:

    . . .

    BEGIN

       SELECT _FeatureNbr

       FROM allFeaturesNumbersTable

       WHERE _FeatureCategory = @theCategoryNbr AND _SymbolNbr = @theSymbolNbr

    END

    Sunday, October 18, 2020 7:27 PM
  • Yes, that actually worked when I changed it like that. @theSymbolNbr and @theCategoryNbr was passed successfully to the stored prodecure?

    Now I am a bit confused of what I should use or how I should write this and why my approach didn't work?

    Sunday, October 18, 2020 7:34 PM
  • If you need dynamic queries, then try something like this:

    EXECUTE sp_executesql

          @FullQuery,

          N'@theSymbolNbr SMALLINT, @theCategoryNbr TINYINT',

          @theSymbolNbr = @theSymbolNbr,

          @theCategoryNbr = @theCategoryNbr

    • Marked as answer by Silvers11 Sunday, October 18, 2020 7:47 PM
    Sunday, October 18, 2020 7:35 PM
  • Viorel, your last code did also work! Thank you!

    It seems that I have not done this correctly in my original post and should declare it like you showed my now if I understand?

    • Marked as answer by Silvers11 Sunday, October 18, 2020 7:51 PM
    • Unmarked as answer by Silvers11 Sunday, October 18, 2020 7:51 PM
    Sunday, October 18, 2020 7:42 PM
  • Yes, the MSDN documentation and samples for sp_executesql suggest the right approach.

    Sunday, October 18, 2020 7:47 PM
  • Thank you, that was very helpful!
    Sunday, October 18, 2020 7:51 PM
  • By the way, try the simplified variant too:

    EXECUTE sp_executesql

          @FullQuery,

          N'@theSymbolNbr SMALLINT, @theCategoryNbr TINYINT',

          @theSymbolNbr, @theCategoryNbr

    Sunday, October 18, 2020 7:54 PM
  • Yes, so it can even be written like this. 

    That worked also. That is even better then. This was really nice to learn.

    Thank you again!

    Sunday, October 18, 2020 8:03 PM