1

I have stored procedure like

CREATE procedure [dbo].[spAddItem]
(
  @itemId nvarchar(50),
  @itemName nvarchar(500),
  @itemDescription nvarchar(500),
  @itemImage nvarchar(100),
  @cityId  bigint,
  @Active int,
  @Status bigint output
)
as
begin tran
  declare @count as int
  declare @result as int  
  Set NOCOUNT ON

  select @count = Count(*) from Item where itemName = @itemName and itemId = @itemId

  if(@count > 0)
           begin
              set @result = -1;
           end
  else  
       begin 
                insert into Item(itemId,itemName,itemDescription,itemImage,cityId,Active)
                values (@itemId,@itemName,@itemDescription,@itemImage,@cityId,@Active) 
                set @result = 0  
        end

        if @@error=0  
          begin  
           commit tran 
           select @Status=@result
           return @Status  
          end  
         else  
          begin  
           rollback tran
           select @Status=@result
           return @Status     
          end

commit tran

& to get the status paremeter in my function i am using function

public void DALInsertItem(ItemBLL it)
    {
        int rowNo;
        using (SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
        {
            try
            {
                SqlParameter[] par = new SqlParameter[7];

                par[0] = new SqlParameter("@itemId",it.itemId);
                par[1] = new SqlParameter("@itemName",it.itemName);
                par[2] = new SqlParameter("@itemDescription", it.itemDescription);
                par[3] = new SqlParameter("@itemImage", it.itemImage);
                par[4] = new SqlParameter("@cityId",it.cityId);
                par[5] = new SqlParameter("@Active", it.itemActive);
                par[6] = new SqlParameter("@Status", 100);
                par[6].Direction = ParameterDirection.Output;

                rowNo = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "spAddItem", par);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }

            if (rowNo == -1)
            {
                it.isExistItem = false;
            }
            else
            {
                it.isExistItem = true;
            }

        }
    }

Problem is I am not getting the status(Output parameter) in my function,

How can I access the status parameter in my function that is set in my stored procedure.

Thanks

0

2 Answers 2

5

You need to access that output parameter after you've called your stored procedure:

using (SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
   try
   {
      SqlParameter[] par = new SqlParameter[7];

      par[0] = new SqlParameter("@itemId",it.itemId);
      par[1] = new SqlParameter("@itemName",it.itemName);
      par[2] = new SqlParameter("@itemDescription", it.itemDescription);
      par[3] = new SqlParameter("@itemImage", it.itemImage);
      par[4] = new SqlParameter("@cityId",it.cityId);
      par[5] = new SqlParameter("@Active", it.itemActive);
      par[6] = new SqlParameter("@Status", 100);
      par[6].Direction = ParameterDirection.Output;

      rowNo = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "spAddItem", par);

      int returnStatus = (int)par[6].Value;
  }
Sign up to request clarification or add additional context in comments.

Comments

1

you are returning @@status from the stored procedure, so you can use ExecuteScalar instead of ExecuteNonQuery to return the status

2 Comments

OK, but that's doesn't really help in understanding how to read out the value of the @Status parameter.....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.