title: How to run stored procedure query between 2 table & insert data base on reference column?
body: <p>I've created a simple stored procedure to update my <code>Sample_TAG_NUMBERTEST</code> table but I need it to get an <code>ItemCode</code> from another table <code>PartItem</code> by using both <code>SOLine</code> &amp; <code>SONbr</code> to do the query but I don't know how to write it.</p>
  
  <p>2nd thing I need to do is when my <code>StatusCode</code> column is <code>N001</code> then my <code>DESC</code> column will automatically insert <code>NEW ORDER</code> data value, for <code>PR002</code> my <code>DESC</code> column will become <code>Progress</code>. </p>
  
  <p>How can I do that? Thanks in advance.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SampleTagNumberUpdate] 
  @sONbr nvarchar(50) = NULL,
  @SOLine nvarchar(50) = NULL,
  @SerialNbr nvarchar(50) = NULL,
  @StatusCode nvarchar(50) = NULL,
  @PackType nvarchar(50) = NULL,
  @PalletID nvarchar(50) = NULL,
  @PackingListNo nvarchar(50) = NULL,
  @ItemCode nvarchar(50) = NULL,
  @CrDateTime nvarchar(50) = NULL,
  @CrUserID nvarchar(50) = NULL,
  @return nvarchar(50) = NULL OUTPUT
  AS
  BEGIN
  SET NOCOUNT ON;
  
      IF EXISTS(SELECT sONbr , SOLine
                FROM [SampleSystem].[dbo].[Sample_TAG_NUMBERTEST]
                WHERE sONbr = @sONbr AND SOLine = @SOLine)
  BEGIN
      UPDATE [SampleSystem].[dbo].[Sample_TAG_NUMBERTEST]
      SET     SerialNbr = @SerialNbr
              ,StatusCode = @StatusCode
              ,PackType = @PackType
              ,PalletID = @PalletID
              ,PackingListNo = @PackingListNo
              ,ItemCode = @ItemCode
              ,LastUpdDateTime = GETDATE()
              ,LastUpdUserID = @CrUserID
  
      WHERE sONbr = @sONbr AND SOLine = @SOLine
  
      IF @@ERROR &lt;&gt; 0
        Set @Return = 'UPDATE FAILED'
      ELSE
        Set @Return = 'UPDATE SUCCESSFULLY'
  END
  ELSE
  BEGIN
     INSERT INTO [SampleSystem].[dbo].[Sample_TAG_NUMBERTEST](SONbr, SOLine, SerialNbr
              ,StatusCode
              ,PackType
              ,PalletID
              ,PackingListNo
              ,ItemCode
              ,CrDateTime
              ,CrUserID)
     VALUES(@sONbr, @SOLine, @SerialNbr, @StatusCode, @PackType
              ,@PalletID
              ,@PackingListNo
              ,@ItemCode
              ,GETDATE()
              ,@CrUserID)
  
     IF @@ERROR &lt;&gt; 0
    Set @Return = 'INSERT DATA FAILED'
     ELSE
        Set @Return = 'INSERT DATA SUCCESSFULLY'
  END
  END
  </code></pre>