title: SQL Server stored procedure and execute in VB.NET
body: <p>This is a bit old-contents to be discussed but I need someone who can explain me how to create stored procedure in SQL Server for returning a value from procedure, example:</p>
  
  <pre><code>SELECT NAME, ADDRESS 
  FROM CUSTOMER 
  WHERE IDCUSTOMER = 'DS212';
  </code></pre>
  
  <p>Then I need the name of its customer and the address instead.</p>
  
  <p>I need to make it as a stored procedure and show me how to execute it on VB.NET. Perhaps we assume that the name will be prompted to LABEL1.TEXT and the address will be prompted to LABEL2.TEXT. </p>
  
  <p>I've improved this SQL-Server Stored Procedure Using return but I have nothing to return after I execute it</p>
  
  <pre><code>CREATE PROCEDURE inserting_customer
              @custId varchar(10),
              @usr_id int
  AS
  BEGIN 
  SET @usr_id = (SELECT MAX(SUBSTRING(CUSTOMER.idCustomer,3, LEN(CUSTOMER.IDCUSTOMER))) FROM CUSTOMER
  WHERE 
  SUBSTRING(CUSTOMER.idCustomer,1,2) = @custId)
  END
  RETURN @usr_id
  GO
  </code></pre>
  
  <p>This is my VB.NET</p>
  
  <pre><code>  conn.Open()
  
          Dim cmd As New SqlCommand("inserting_customer", conn)
  
          Try
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Add("@custId", SqlDbType.VarChar)
              cmd.Parameters("@custId").Value = "YW"
              cmd.Parameters.Add("@usr_id", SqlDbType.Int)
              cmd.Parameters("@usr_id").Value = 0
              cmd.ExecuteNonQuery()
          Finally
              If cmd IsNot Nothing Then cmd.Dispose()
              If conn IsNot Nothing AndAlso conn.State &lt;&gt; ConnectionState.Closed Then conn.Close()
          End Try
  </code></pre>