Why is an output parameter is expected in this stored procedure?

Question

I have written a simple stored procedure with some output parameters, but when I execute it from my C# code, I get an error

Expects Parameters

It is expecting me provide values for the OUTPUT parameters ...

Please help me figure out the error ...

My stored procedure:

ALTER PROCEDURE [dbo].[Check_EntryTrade_value] 
    @Account_ID bigint,
    @Date datetime2(7),
    @tradeby varchar(20),
    @Symbol varchar(20),
    @B_S varchar(20),
    @RateE float ,
    @Usd_Marg bigint OUTPUT,
    @Amount bigint OUTPUT,
    @Rate float OUTPUT,
    @Entry_Order_id bigint OUTPUT
AS
BEGIN
    SELECT 
        @Rate = [Rate], 
        @Entry_Order_id = [EntryOrder_ID],
        @Amount = [Amount],
        @Usd_Marg = [Usd_Mrg]  
    FROM  
        [dbo].[entrytable] 
    WHERE
        [B_S]  = @B_S 
        AND [Rate] = @RateE 
        AND [Account_ID] = @Account_ID 
        AND [Symbol] = @Symbol;

    IF (@Rate IS NOT NULL)
       INSERT INTO neworder (Account_ID, Symbol, B_S, Rate, Amount, Date, Usd_Marg, tradeby) 
       VALUES(@Account_ID, @Symbol, @B_S, @Rate, @Amount, @Date, @Usd_Marg, @tradeby);

    DELETE FROM entrytable WHERE EntryOrder_ID = @Entry_Order_id;
END

And this is my C# Code:

SqlConnection conn = new SqlConnection(Properties.Settings.Default.MyCon);
conn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;

//SQL INJECTION '"+ +"'
cmd.CommandText = "Check_EntryTrade_value";
SqlParameter pID1 = new SqlParameter("@Account_ID", SqlDbType.BigInt, 10);
pID1.Value = 1;
SqlParameter pID2 = new SqlParameter("@Symbol", SqlDbType.VarChar, 20);
pID2.Value = "EUR/USD";
SqlParameter pID3 = new SqlParameter("@B_S", SqlDbType.VarChar, 20);
pID3.Value = "Buy";
SqlParameter pID4 = new SqlParameter("@RateE", SqlDbType.Float, 10);
pID4.Value = 1.24763;
//SqlParameter pID6 = new SqlParameter("@Amount", SqlDbType.BigInt, 10);
//pID6.Value = 1;
SqlParameter pID5 = new SqlParameter("@Date", SqlDbType.DateTime2, 7);
pID5.Value = DateTime.Now.ToShortDateString();
SqlParameter pID6 = new SqlParameter("@tradeby", SqlDbType.VarChar, 20);
pID6.Value = "User";

cmd.Parameters.Add(pID1);
cmd.Parameters.Add(pID2);
cmd.Parameters.Add(pID3);
cmd.Parameters.Add(pID4);
cmd.Parameters.Add(pID5);
cmd.Parameters.Add(pID6);

cmd.ExecuteNonQuery();

Running my C# code returns this error:

Procedure or Function 'Check_EntryTrade_value' expects parameter '@Usd_Marg', which was not supplied.

You need to add SqlParameters for the output parameters as well. Check their Value after you execute it. How else are you expecting to get their values? — Blorgbeard, Nov 14 '14 at 03:22
@Blorgbeard, i dont want any ouput from the Stored procedure i just want it to be Executed after giving it, its Required Fields, — Muhammad Al-Baloushi, Nov 14 '14 at 03:37

score 1 · Answer 1 · edited Nov 14 '14 at 06:05

I Found it :) .. Thanks Every One for Help .. i love StackOverFlow :P

Here is what i did :

//STEP-1 CONNECTION
SqlConnection conn = new SqlConnection(Properties.Settings.Default.MyCon);
  //STEP-2 COmmand
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
//SQL INJECTION '"+ +"'
cmd.CommandText = "Check_EntryTrade_value";


SqlParameter pID1 = new SqlParameter("@Account_ID", SqlDbType.BigInt, 10);
pID1.Value = 1;
SqlParameter pID2 = new SqlParameter("@Symbol", SqlDbType.VarChar, 20);
pID2.Value = "EUR/USD";
SqlParameter pID3 = new SqlParameter("@B_S", SqlDbType.VarChar, 20);
pID3.Value = "Buy";
SqlParameter pID4 = new SqlParameter("@RateE", SqlDbType.Float, 10);
pID4.Value = 1.24763;
//SqlParameter pID6 = new SqlParameter("@Amount", SqlDbType.BigInt, 10);
//pID6.Value = 1;
SqlParameter pID5 = new SqlParameter("@Date", SqlDbType.DateTime2, 7);
pID5.Value = DateTime.Now.ToShortDateString();
SqlParameter pID6 = new SqlParameter("@tradeby", SqlDbType.VarChar, 20);
pID6.Value = "User";

SqlParameter pID7 = new SqlParameter("@Usd_Marg", SqlDbType.BigInt, 10);
pID7.Direction = ParameterDirection.Output;
// pID6.Value = "User";
SqlParameter pID8 = new SqlParameter("@Amount", SqlDbType.BigInt, 20);
pID8.Direction = ParameterDirection.Output;
//  pID6.Value = "User";
SqlParameter pID9 = new SqlParameter("@Rate", SqlDbType.BigInt, 20);
pID9.Direction = ParameterDirection.Output;
// pID6.Value = "User";
SqlParameter pID10 = new SqlParameter("@Entry_Order_id", SqlDbType.BigInt, 20);
pID10.Direction = ParameterDirection.Output;
// pID6.Value = "User";




cmd.Parameters.Add(pID1);
cmd.Parameters.Add(pID2);
cmd.Parameters.Add(pID3);
cmd.Parameters.Add(pID4);
cmd.Parameters.Add(pID5);
cmd.Parameters.Add(pID6);
cmd.Parameters.Add(pID7);
cmd.Parameters.Add(pID8);
cmd.Parameters.Add(pID9);
cmd.Parameters.Add(pID10);

Actually, this should be `using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.MyCon) { using (SqlCommand cmd = new SqlCommand()) { // rest of the code }}` — John Saunders, Nov 14 '14 at 06:06

score -1 · Answer 2 · edited Nov 14 '14 at 06:03

Try to remove

@Usd_Marg bigint OUTPUT,
@Amount bigint OUTPUT,
@Rate float OUTPUT,
@Entry_Order_id bigint OUTPUT

from stored procedure definition .

Modify your query :

SELECT [Rate], [EntryOrder_ID],[Amount],[Usd_Mrg]  
FROM [dbo].[entrytable] 
WHERE [B_S]  = @B_S AND [Rate] = @RateE AND [Account_ID] = @Account_ID AND [Symbol] = @Symbol;

On the C# side you will receive a data set so you need to change last line of c# code :

cmd.ExecuteScalar();

Why is an output parameter is expected in this stored procedure?

2 Answers2