title: Using ScopeIdentity for an identity column in a stored procedure, asp.net
body: <p>I've created a stored procedure and I'm inserting values into 3 tables, <code>Users</code>, <code>Workers</code> and <code>Vets</code>. In the <code>Users</code> table, I have an identity column <code>UserID</code>, which is a foreign key to the <code>Workers</code> and <code>Vets</code> table. </p>
  
  <p>When inserting into the <code>Workers</code> and <code>Vets</code> tables, I can't seem to retrieve the value of the <code>UserID</code> that was inserted into the <code>Users</code> table. </p>
  
  <p>This is the stored procedure for the vets:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[InsertIntoVets]
      @PrimeiroNome Varchar(20),
      @NomeDoMeio Varchar(50),
      @Sobrenome Varchar(30),
      @DataDeNascimento Date,
      @EndereçoPostal1 Varchar(120),
      @EndereçoPostal2 Varchar(120),
      @Cidade Varchar(100),
      @Especialidade_id Int,
      @Clinica_id Int,
      @UserID Int OUTPUT 
  AS 
  BEGIN 
      SET NOCOUNT ON;
  
      INSERT INTO Veterinarios([Primeiro nome], [Nome do meio], Sobrenome, [Data de Nascimento], [Endereço Postal1], [Endereço Postal2], Cidade, Especialidade_id, Clinica_id, UserID)
      VALUES (@PrimeiroNome, @NomeDoMeio, @Sobrenome, @DataDeNascimento, @EndereçoPostal1, @EndereçoPostal2, @Cidade, @Especialidade_id, @Clinica_id, @UserID)
  
      SET @UserID = SCOPE_IDENTITY();
  END
  </code></pre>
  
  <p>C# code:</p>
  
  <pre><code>// Vets
  SqlCommand sqlCommandVets = new SqlCommand();
  sqlCommandVets.CommandType = System.Data.CommandType.StoredProcedure;
  sqlCommandVets.CommandText = "InsertIntoVets";
  
  sqlCommandVets.Parameters.Add("@PrimeiroNome", SqlDbType.VarChar).Value = (primeiroNome.Text.Trim());
  sqlCommandVets.Parameters.Add("@NomeDoMeio", SqlDbType.VarChar).Value = (nomeDoMeio.Text.Trim());
  sqlCommandVets.Parameters.Add("@Sobrenome", SqlDbType.VarChar).Value = (sobrenome.Text.Trim());
  sqlCommandVets.Parameters.Add("@DataDeNascimento", SqlDbType.Date).Value = (dataDeNascimento.Text.Trim());
  sqlCommandVets.Parameters.Add("@EndereçoPostal1", SqlDbType.VarChar).Value = (enderecoPostal1.Text.Trim());
  sqlCommandVets.Parameters.Add("@EndereçoPostal2", SqlDbType.VarChar).Value = (enderecoPostal2.Text.Trim());
  sqlCommandVets.Parameters.Add("@Cidade", SqlDbType.VarChar).Value = (cidade.Text.Trim());
  sqlCommandVets.Parameters.Add("@Especialidade_id", SqlDbType.Int).Value = (dropDownEspecialidade.SelectedValue);
  sqlCommandVets.Parameters.Add("@Clinica_id", SqlDbType.VarChar).Value = (dropDownClinica.SelectedValue);
  sqlCommandVets.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
  
  sqlCommandVets.Connection = con;
  </code></pre>
  
  <p>Here you have a screenshot of both the <code>Users</code> and <code>Vets</code> table:</p>
  
  <p><a href="https://i.stack.imgur.com/TCZC9.png" rel="nofollow noreferrer">UsersTable</a> AND <a href="https://i.stack.imgur.com/4Rat0.png" rel="nofollow noreferrer">VetsTable</a></p>
  
  <p>I hope I was clear and provided enough info.</p>
  
  <p>Thank you in advance,
  Hugo Silva.</p>