title: SQL Merge. Update when exits, insert if it doesn't, using variables in stored procedure
body: <p>Never used <code>MERGE</code> before nor Sybase ASE 15.7</p>
  
  <p>I need a stored procedure for updating existing data or insert it if it doesn't exist.
  I know how to do it without <code>MERGE</code> but I want to learn to use it.</p>
  
  <p>This is my table and test data:</p>
  
  <pre><code>CREATE TABLE myTable(
      col1    Smallint    Not NULL,
      col2    Char(15)    Not NULL,
      col3    Char(2)     Not NULL,
      col4  BIT   Not NULL,
      col5 varchar(100) NULL,
      Constraint PK_myTable primary key clustered (col1,col2,col3)
  )
  go
  
  insert into myTable values( 1,'A','1',1,'A')
  go
  </code></pre>
  
  <p>My procedure</p>
  
  <pre><code>    create procedure spInsertUpdateMytable(
          @col1   Smallint,
          @col2   Char(15),
          @col3   Char(2),
          @col4   BIT,
          @col5   varchar(100))
      AS
  
      merge into myTable as V
           using (select col1, col2, col3, col4, col5 from myTable
                where @col1=col1 and @col2 = col2 and @col3=col3) as N
           ON v.col1=n.col1 and v.col2=n.col2 and v.col3=n.col3
           when not matched then
              insert (col1, col2, col3, col4, col5)
               values( @col1, @col2, @col3, @col4, @col5)
           when matched
              then update set
                v.col4 = @col4, v.col5 = @col5
  go
  </code></pre>
  
  <p>update seems to work, but insert does not.</p>
  
  <pre><code>exec spInsertUpdateMytable  1,'A','1',0,'B' --Update is done ok.
  
  exec spInsertUpdateMytable  1,'C','1',0,'Z' --No new record added
  </code></pre>
  
  <p>I can't realize what I'm doing wrong, I'm following this specs
  <a href="http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1570/html/commands/commands84.htm" rel="nofollow noreferrer">Adaptive Server Enterprise 15.7 > Reference Manual: Commands > Commands
  </a></p>