title: How to return output parameter from stored procedure and catch/handle in C#?
body: <p>I have created a stored procedure in SQL Server 2012. The stored procedure has an output parameter of type <code>varchar(15)</code>:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Proc_Authenticate]
      (@username int, 
       @password varchar(25),
       @Error varchar(15) output)
  AS
  BEGIN 
      DECLARE @count INT
  
      SET @count = (SELECT COUNT(*) 
                    FROM loginmaster 
                    WHERE userid = @userid AND password = @password)
  
      IF (@count = 0)
      BEGIN
          SET @Error = 'Error1'
      END
  END
  </code></pre>
  
  <p>When I run the stored procedure from SSMS, it is working properly, but when I call the stored procedure from C#, with the help of ExecuteScalar, it is not working as expected. <code>ExecuteScalar</code> always returns null. If the same same thing I do with <code>ExecuteNonQuery</code>, it is working properly. <code>ExecuteNonQuery</code> is used for insert/update. <code>ExeuteNonQuery</code> returns number of row affected rows as int value:</p>
  
  <pre><code>Int rowaffected = cmd.ExecuteNonQuery()
  </code></pre>
  
  <p>Normally I check if <code>rowaffected</code> is greater than 0, take value output parameter.</p>
  
  <pre><code>if(rowaffected  &gt; 0)
  {
    string s = sqlparameterError.value.Tostring()
  }
  </code></pre>
  
  <p>Here how can I check? <code>rowaffected</code> is always -1. Is it right way to check it?</p>
  
  <p>I have changed my code. I removed output parameter. I have created a variable and taken in select. With the help of <code>ExecuteScalar</code> I am able to get value of it.</p>
  
  <p>Modified stored procedure is like this:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Proc_Authenticate]
      (@username int,   
       @password varchar(25))
  AS
  BEGIN 
      DECLARE @count INT 
      DECLARE @Error VARCHAR(15) 
  
      SET @count = (SELECT COUNT(*) 
                    FROM loginmaster 
                    WHERE userid = @userid and password = @password)
  
      IF (@count = 0)
      BEGIN
          SET @Error = 'Error1'
      END
  
      SELECT @Error AS Error
  END
  </code></pre>