locked
Return Value from Stored Procedure After Insert/Update RRS feed

  • Question

  • User-1499457942 posted

    hi

      I want to return integer value from Stored Procedure to check if record is successfully Inserted or Updated & then display message in c#

    ALTER PROCEDURE [dbo].[sp_Test]
          @Action VARCHAR(1)
    	  ,@No int = Null
          ,@Code VARCHAR(10) = NULL
    	  ,@Status VARCHAR(1) = Null
    	  ,@Result INT OUTPUT
    AS
    BEGIN
          SET NOCOUNT ON;
     
     
          --INSERT
          IF @Action = 'I'
          BEGIN
                INSERT INTO dbo.Test(Code,Status)
                VALUES (@Code,'A')
          END
     
          --UPDATE
          IF @Action = 'U'
          BEGIN
                UPDATE  dbo.Test
                SET Code = @Code WHERE No = @No
          END
    END
    

    Thanks

    Monday, August 20, 2018 5:36 AM

Answers

  • User-369506445 posted

    you have to check your rows update

     if (@@ROWCOUNT = 0)
    	SET @Returns = 0
    else
            SET @Returns = 1

    change your sp to below

     
    
    
    Alter PROCEDURE [dbo].[sp_Test]
          @Action VARCHAR(1)
    	  ,@No int = Null
          ,@Code VARCHAR(10) = NULL
    	  ,@Status VARCHAR(1) = Null	  
    AS
    DECLARE @Returns BIT 
    DECLARE @RowCount INTEGER
    
    BEGIN
          SET NOCOUNT ON;
     
     SET @Returns = 0
          --INSERT
          IF @Action = 'I'
          BEGIN
                INSERT INTO dbo.Test(Code,Status)
                VALUES (@Code,'A')
    			SET @Returns = 1
          END
     
          --UPDATE
          IF @Action = 'U'
          BEGIN
                UPDATE  dbo.Test
                SET Code = @Code WHERE [No] = @No
    			if (@@ROWCOUNT = 0)
    			  	SET @Returns = 0
    			else
                                    SET @Returns = 1
          END
    	  RETURN @Returns
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 20, 2018 7:00 AM

All replies

  • User1992938117 posted

    Instead of using ExecuteNonQuery() use ExecuteScaler(), will give you primary key as return,

    cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";  
    Int32 count = (Int32) cmd.ExecuteScalar();

    https://www.aspsnippets.com/Articles/Difference-between-ExecuteReader-ExecuteScalar-and-ExecuteNonQuery.aspx 

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?redirectedfrom=MSDN&view=netframework-4.7.2#System_Data_SqlClient_SqlCommand_ExecuteScalar 

    Monday, August 20, 2018 5:41 AM
  • User-1499457942 posted

    Hi

       I want to return value in result as 0,1 . If 0 then some error else 1.

    Thanks

    Monday, August 20, 2018 5:49 AM
  • User-369506445 posted

    hi

    please try below

    Alter PROCEDURE [dbo].[sp_Test]
          @Action VARCHAR(1)
    	  ,@No int = Null
          ,@Code VARCHAR(10) = NULL
    	  ,@Status VARCHAR(1) = Null
    	  ,@Result INT OUTPUT
    AS
    DECLARE @Returns BIT 
    
    BEGIN
          SET NOCOUNT ON;
     
     SET @Returns = 0
          --INSERT
          IF @Action = 'I'
          BEGIN
                INSERT INTO dbo.Test(Code,Status)
                VALUES (@Code,'A')
    			SET @Returns = 1
          END
     
          --UPDATE
          IF @Action = 'U'
          BEGIN
                UPDATE  dbo.Test
                SET Code = @Code WHERE [No] = @No
    			SET @Returns = 1
          END
    	  RETURN @Returns
    END

    and in your <g class="gr_ gr_32 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="32" data-gr-id="32">code</g> you can get it below like

     string ReturnValue = com.ExecuteNonQuery().ToString();

    Monday, August 20, 2018 5:51 AM
  • User-1499457942 posted

    Hi

      Below line it sould not be int . Since we are returning Bit i.e 0 or 1

    string ReturnValue = com.ExecuteNonQuery().ToString();

    Thanks
    Monday, August 20, 2018 6:03 AM
  • User-369506445 posted

    <g class="gr_ gr_16 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="16" data-gr-id="16">yes ,</g> it was a <g class="gr_ gr_31 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="31" data-gr-id="31">sample ,</g> you can use 

      int  ReturnValue = com.ExecuteNonQuery();

    Monday, August 20, 2018 6:13 AM
  • User-1499457942 posted

    Hi

      I am trying like this it is returning -1

    int Result = Convert.ToInt32(cmd.ExecuteNonQuery().ToString());

    Thanks

    Monday, August 20, 2018 6:14 AM
  • User-369506445 posted

    your code should like below 

    using (var con = new SqlConnection("Data Source=.;Initial Catalog=MyDatabase;trusted_connection=true"))
                {
                    using (System.Data.SqlClient.SqlCommand com = new SqlCommand("sp_Test", con))
                    {
                        con.Open();
                        com.CommandText = "sp_Test";
                        com.CommandType = System.Data.CommandType.StoredProcedure;
                        com.Parameters.AddWithValue("@Action", 'I');
                        com.Parameters.AddWithValue("@No", 1);
                        com.Parameters.AddWithValue("@Code", '1');
                        com.Parameters.AddWithValue("@Status", '1');
    
                        SqlParameter returnParameter = com.Parameters.Add("Returns", SqlDbType.Int);
                        returnParameter.Direction = ParameterDirection.ReturnValue;
                        com.ExecuteNonQuery();
                        int returnValue = (int)returnParameter.Value;
                    }
                }

    Monday, August 20, 2018 6:24 AM
  • User1992938117 posted

    I am trying like this it is returning -1

    Use out <g class="gr_ gr_16 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="16" data-gr-id="16">paremeter</g>

    https://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx 

    https://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/ 

    Monday, August 20, 2018 6:31 AM
  • User-1499457942 posted

    Hi

      In Stored procedure don't we need to define

    ,@Returns INT OUTPUT


    Secondly i have written below code it is giving error - Object reference not set to an instance of the Object.

    SqlParameter returnParameter = cmd.Parameters.Add("Returns", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;
    con.Open();
    int Result = (int)returnParameter.Value;

    Thanks

    Monday, August 20, 2018 6:34 AM
  • User-369506445 posted

    <g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="10" data-gr-id="10">yes <g class="gr_ gr_9 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" id="9" data-gr-id="9">,</g></g><g class="gr_ gr_9 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" id="9" data-gr-id="9">you</g> don't need to define it 

    your complete sp 

    Alter PROCEDURE [dbo].[sp_Test]
          @Action VARCHAR(1)
    	  ,@No int = Null
          ,@Code VARCHAR(10) = NULL
    	  ,@Status VARCHAR(1) = Null	  
    AS
    DECLARE @Returns BIT 
    
    BEGIN
          SET NOCOUNT ON;
     
     SET @Returns = 0
          --INSERT
          IF @Action = 'I'
          BEGIN
                INSERT INTO dbo.Test(Code,Status)
                VALUES (@Code,'A')
    			SET @Returns = 1
          END
     
          --UPDATE
          IF @Action = 'U'
          BEGIN
                UPDATE  dbo.Test
                SET Code = @Code WHERE [No] = @No
    			SET @Returns = 0
          END
    	  RETURN @Returns
    END

    and your code

    using (var con = new SqlConnection("Data Source=.;Initial Catalog=School;trusted_connection=true"))
                {
                    using (System.Data.SqlClient.SqlCommand com = new SqlCommand("sp_Test", con))
                    {
                        con.Open();
                        com.CommandText = "sp_Test";
                        com.CommandType = System.Data.CommandType.StoredProcedure;
                        com.Parameters.AddWithValue("@Action", 'I');
                        com.Parameters.AddWithValue("@No", 1);
                        com.Parameters.AddWithValue("@Code", '1');
                        com.Parameters.AddWithValue("@Status", '1');
    
                        SqlParameter returnParameter = com.Parameters.Add("Returns", SqlDbType.Bit);
                        returnParameter.Direction = ParameterDirection.ReturnValue;
                        com.ExecuteNonQuery();
                        bool returnValue = Convert.ToBoolean( returnParameter.Value);
                    }
                }

    Monday, August 20, 2018 6:35 AM
  • User-369506445 posted

    I put full sample code <g class="gr_ gr_25 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="25" data-gr-id="25">here <g class="gr_ gr_24 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" id="24" data-gr-id="24">,</g></g><g class="gr_ gr_24 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" id="24" data-gr-id="24">and</g> it <g class="gr_ gr_48 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="48" data-gr-id="48">works ,</g> please try it 

    Monday, August 20, 2018 6:38 AM
  • User-1716253493 posted

    Use select @@ROWCOUNT

    Monday, August 20, 2018 6:45 AM
  • User-1499457942 posted

    Hi

      I have below code . record is not getting updated but still it is returning True

    IF @Action = 'U'
          BEGIN
                UPDATE  dbo.Test
                SET Code = @Code WHERE No = @No
                Set @Returns = 1
          END


    SqlParameter returnParameter = cmd.Parameters.Add("Returns", SqlDbType.Bit);
    returnParameter.Direction = ParameterDirection.ReturnValue;
    con.Open();
    cmd.ExecuteNonQuery();
    bool Result = Convert.ToBoolean(returnParameter.Value);

    Thanks

    Monday, August 20, 2018 6:47 AM
  • User-369506445 posted

    you have to check your rows update

     if (@@ROWCOUNT = 0)
    	SET @Returns = 0
    else
            SET @Returns = 1

    change your sp to below

     
    
    
    Alter PROCEDURE [dbo].[sp_Test]
          @Action VARCHAR(1)
    	  ,@No int = Null
          ,@Code VARCHAR(10) = NULL
    	  ,@Status VARCHAR(1) = Null	  
    AS
    DECLARE @Returns BIT 
    DECLARE @RowCount INTEGER
    
    BEGIN
          SET NOCOUNT ON;
     
     SET @Returns = 0
          --INSERT
          IF @Action = 'I'
          BEGIN
                INSERT INTO dbo.Test(Code,Status)
                VALUES (@Code,'A')
    			SET @Returns = 1
          END
     
          --UPDATE
          IF @Action = 'U'
          BEGIN
                UPDATE  dbo.Test
                SET Code = @Code WHERE [No] = @No
    			if (@@ROWCOUNT = 0)
    			  	SET @Returns = 0
    			else
                                    SET @Returns = 1
          END
    	  RETURN @Returns
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 20, 2018 7:00 AM