title: Why does SQL server passes a value to a OUTPUT variable in a stored procedure?
body: <p>I have a function which calls a stored procedure with providing its parameters.</p>
  
  <pre><code>int8 CDBAgent::CreateKnights(uint16 sClanID, uint8 bNation, string &amp; strKnightsName, string &amp; strChief, uint8 bFlag)
  {
      int8 bRet = -1;
      unique_ptr&lt;OdbcCommand&gt; dbCommand(m_GameDB-&gt;CreateCommand());
      if (dbCommand.get() == nullptr)
          return bRet;
  
  
      dbCommand-&gt;AddParameter(SQL_PARAM_OUTPUT, &amp;bRet);
      dbCommand-&gt;AddParameter(SQL_PARAM_INPUT, strKnightsName.c_str(), strKnightsName.length());
      dbCommand-&gt;AddParameter(SQL_PARAM_INPUT, strChief.c_str(), strChief.length());
  
      if (!dbCommand-&gt;Execute(string_format(_T("{? = CALL CREATE_KNIGHTS ( %d, %d, %d, ?, ?)}"), sClanID, bNation, bFlag)))
          ReportSQLError(m_GameDB-&gt;GetError());
  
  
      return bRet;
  }
  </code></pre>
  
  <p>and the stored procedure;</p>
  
  <pre><code>USE [KN_online]
  GO
  /****** Object:  StoredProcedure [dbo].[CREATE_KNIGHTS]    Script Date: 09/04/2016 03:09:46 ******/
  SET ANSI_NULLS OFF
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- Batch submitted through debugger: SQLQuery4.sql|7|0|C:\Users\Leth\AppData\Local\Temp\~vs3286.sql
  
  /****** Object:  Stored Procedure dbo.CREATE_KNIGHTS    Script Date: 6/6/2006 6:03:32 PM ******/
  
  -- modify by sungyong 2002.09.27
  
  ALTER PROCEDURE [dbo].[CREATE_KNIGHTS]
  @nRet       smallint OUTPUT,
  @index      smallint,
  @nation     tinyint,
  @community  tinyint,
  @strName    char(21), 
  @strChief   char(21)
  
  
  AS
  
  DECLARE @Row tinyint, @knightsindex smallint, @knightsname char(21)
      SET @Row = 0    SET @knightsindex = 0  SET @knightsname = ''
  
      SELECT @Row = COUNT(*) FROM KNIGHTS WHERE IDNum = @index or  IDName = @strName
  
      IF @Row &gt; 0 or @index = 0
      BEGIN
          SET @nRet =  3
          RETURN
      END
  
      --SELECT @Row = COUNT(*) FROM KNIGHTS WHERE IDName = @strName
  
      --IF @Row &gt; 0
  --  BEGIN
      --  SET @nRet =  3
          --RETURN
  --  END
  
  BEGIN TRAN  
  
      INSERT INTO KNIGHTS ( IDNum, Nation, Flag, IDName, Chief  ) 
      VALUES   (@index, @nation, @community, @strName, @strChief )
  
      INSERT INTO KNIGHTS_USER ( sIDNum, strUserID  ) 
      VALUES   (@index,  @strChief )
  
      IF @@ERROR &lt;&gt; 0
      BEGIN    
          ROLLBACK TRAN 
          SET @nRet =  6
          RETURN
      END
  
  --  UPDATE USERDATA SET Knights = @index, Fame = 1 WHERE strUserId = @strChief  -- 1 == Chief Authority
  
      IF @@ERROR &lt;&gt; 0
      BEGIN    
          ROLLBACK TRAN 
          SET @nRet =  6
          RETURN
      END
  
  COMMIT TRAN
  SET @nRet =  0
  </code></pre>
  
  <p>The problem is when the stored procedure is called it passes the value of sClanID to bRet which is something I found by executing the statement directly on the SQL server (2008 R2) because when I directly executing like;</p>
  
  <pre><code>exec CREATE_KNIGHTS 15001, 2, 1, "OpenKO", 'test'
  </code></pre>
  
  <p>it tries to convert char (OpenKO) to tinyint, and if I give a number as strName instead of OpenKO, it says procedure expects a parameter to @strChief, which was not supplied. </p>
  
  <p>I have other functions with the same structure and they are perfectly fine in the manner of taking parameters correctly and returning the output but why does it not ? and How can I solve this ?</p>
  
  <p>If you like you can see the whole project on <a href="http://github.com/srmeier/KnightOnline" rel="nofollow">github</a></p>
  
  <p>For SEO to this question, as I mentioned below, If I enter the sClanID as words, it gives the error "Error converting data type char to tinyint" and if I enter numbers, it gives "expects parameter @statement, which was not supplied"</p>