DB2 insert row if not exist with value from parame

问题:

I want to insert a row into a table if it doesn't exists yet. I have following code that executes directly:

insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
select 'Default', 'Default', 2014, '', 0, 0
from sysibm.sysdummy1
WHERE NOT EXISTS (SELECT * FROM MyTable 
WHERE Column1 = 'c1'
AND Column2 = 'c2'
AND Year = 2014)

However when i change this to a paramtered method by year:

insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
select 'Default', 'Default', @year, '', 0, 0
from sysibm.sysdummy1
WHERE NOT EXISTS (SELECT * FROM MyTable 
WHERE Column1 = 'c1'
AND Column2 = 'c2'
AND Year = @year)

Also tried with a declared variable in an atomic part with no success:

BEGIN ATOMIC
DECLARE varJaar INTEGER;
SET varYear = @year;
insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
select 'Default', 'Default', varYear , '', 0, 0
from sysibm.sysdummy1
WHERE NOT EXISTS (SELECT * FROM MyTable 
WHERE Column1 = 'c1'
AND Column2 = 'c2'
AND Year = varYear )
END;

I cannot execute this because i cannot use a parameter in my select part. Tried to modify @year to @year as Year, but still no success.

Is there a way i can declare a temp variable or something to use this parameter in my select part?

回答1:

Solved this eventually by creating a temp table. The variable is stored temporarily. This table should be cleared automatically however i still perform a drop table command in a try-catch block before triggering my method:

Try
    Dim adapter As New DataAdapter()
    Dim commandText As String = "drop table QTEMP.tempVariable"
    Dim command As DbCommand = adapter.CreateDbCommand(commandText)

    adapter.ExecuteNonQuery(command)

 Catch notHandledEx As Exception
 End Try

 create table #tempVariable (year int);
 insert into #tempVariable values(@Year);

 insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
 select 'Default', 'Default',  (select max(year) from #tempVariable), '', 0, 0
 from sysibm.sysdummy1
 WHERE NOT EXISTS (SELECT * FROM MyTable 
 WHERE Column1 = 'c1'
 AND Column2 = 'c2'
 AND Year = @Year)