title: Auto Increment Id in stored procedure not working
body: <p>I am trying to get company id like "Cp-00001". If data exists in table then the id should be "Cp-00001" + 1 = "Cp=00002" and do on... </p>
  
  <p>Here's what I have so far: </p>
  
  <pre><code>CREATE PROCEDURE [dbo].[sp_AutoGenerateCustomerCode] 
   AS
  DECLARE @id VARCHAR(10)
  BEGIN
      SELECT @id = 'Cp-' + CAST(MAX(CAST(SUBSTRING(CompanyCode,4,5) AS INTEGER))+1 AS VARCHAR) FROM [Beauty Saloon Project].[dbo].[tbl_Company];
      IF @id IS NULL
      BEGIN
          SET @id = 'Cp-00001';
      END
  
      RETURN @id;
  END
  </code></pre>
  
  <p>but when i call it here </p>
  
  <pre><code>  datatable DT = new datatable
    DT = ExecuteSpDataTable("sp_AutoGenerateCustomerCode");
  </code></pre>
  
  <p>This returns <code>null</code>.</p>
  
  <p>If I don't have data then it should return Cp-00001, but I have one data row in which company code is saloon is it the reason for null ???</p>
  
  <p>EDIT:</p>
  
  <pre><code>public DataTable ExecuteSpDataTable(string SPName)
      {
          try
          {
              if (ConnectionOpen())
              {
                  SqlCommand objSqlCommand = new SqlCommand(SPName, objConnection);
                  objSqlCommand.CommandType = CommandType.StoredProcedure;
                  objSqlCommand.CommandTimeout = 10000;
                  SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter();
                  DataTable objDataTable = new DataTable();
                  objSqlDataAdapter.SelectCommand = objSqlCommand;
                  objSqlDataAdapter.Fill(objDataTable);
                  ConnectionClose();
                  return objDataTable;
              }
              return null;
          }
          catch (Exception ex)
          {
              objErrorLogs.LogError(ex);
              return null;
          }
      }
  </code></pre>