August 1, 2011 at 10:50 am
I have a merge statement I want to encapsulate into an SP. Then I want to be able to pass the sp a year and have the merge statement execute only for that year. Here's what I have so far, almost there. Will someone please tell me how to pass the year parameter to the sp?
First the tables:
create table old
(id smallint primary key not null,
fYear smallint not null,
budjet decimal not null
)
create table new
(id smallint primary key not null,
fYear smallint not null,
budjet decimal not null
)
insert into old (id, fYear, budjet) values (1, 2009, 400),(2, 2010, 500), (3, 2011, 200), (4, 2012, 300), (5, 2001, 100),(6, 2002, 200);
Next the stored procedure/merge:
create procedure testsp_get_fYear
(
@year smallint
)
as
merge new as T
using old as S
on S.id = T.id
when matched and S.fYear = @year then update set fYear = T.fYear, budjet = T.budjet
when not matched and s.fYear = 2011 then insert (id, fYear, budjet) values (s.id, s.fYear, S.budjet);
go
Finally, trying to execute:
exec testsp_get_fYear 2010
I am getting '0 row(s) affected'. How can I swing this?
August 1, 2011 at 11:01 am
I found problem. I needed to include the variable in both places where the 'not matched' and 'matched' conditions are specified.
EG:
create procedure testsp_get_fYear
(
@year smallint
)
as
merge new as T
using old as S
on S.id = T.id
when matched and S.fYear = @year then update set fYear = T.fYear, budjet = T.budjet
when not matched and s.fYear = @year then insert (id, fYear, budjet) values (s.id, s.fYear, S.budjet);
go
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply