I want to use IF-Else in Insert Query in sproc. I have tried this code, and getting error. What's wrong?
create procedure sp_m_ii_2_kaydet
@mtu int,
@mod int,
@moid int,
@uid int
as
insert into m_ii2_alisfatura
(muhtur, mturid, muhodm, modmid, uid) values
(@mtu,
IF (@mtu = 3) SELECT MAX(id) FROM m_ii1_vsf_personel
ELSE IF (@mtu = 2) SELECT MAX(id) FROM m_ii1_vsf_firma
ELSE IF (@mtu = 4) SELECT MAX(id) FROM m_ii1_vsf_taseron,
@mod, @moid, @uid)
That's not a valid syntax try this
You need to use three different insert statements
CREATE PROCEDURE Sp_m_ii_2_kaydet @mtu INT,
@mod INT,
@moid INT,
@uid INT
AS
IF ( @mtu = 3 )
INSERT INTO m_ii2_alisfatura
(muhtur,mturid,muhodm,modmid,uid)
SELECT @mtu,Max(id),@mod,@moid,@uid
FROM m_ii1_vsf_personel
ELSE IF ( @mtu = 2 )
INSERT INTO m_ii2_alisfatura
(muhtur,mturid,muhodm,modmid,uid)
SELECT @mtu,Max(id),@mod,@moid,@uid
FROM m_ii1_vsf_firma
ELSE IF ( @mtu = 4 )
INSERT INTO m_ii2_alisfatura
(muhtur,mturid,muhodm,modmid,uid)
SELECT @mtu,Max(id),@mod,@moid,@uid
FROM m_ii1_vsf_taseron
or Use union all by filtering the data in where clause
CREATE PROCEDURE Sp_m_ii_2_kaydet @mtu INT,
@mod INT,
@moid INT,
@uid INT
AS
INSERT INTO m_ii2_alisfatura
(muhtur,mturid,muhodm,modmid,uid)
SELECT @mtu,Max(id),@mod,@moid,@uid
FROM m_ii1_vsf_personel
WHERE @mtu = 3
UNION ALL
SELECT @mtu,Max(id),@mod,@moid,@uid
FROM m_ii1_vsf_firma
WHERE @mtu = 2
UNION ALL
SELECT @mtu,Max(id),@mod,@moid,@uid
FROM m_ii1_vsf_taseron
WHERE @mtu = 4
or even case statement
CREATE PROCEDURE Sp_m_ii_2_kaydet @mtu INT,
@mod INT,
@moid INT,
@uid INT
AS
INSERT INTO m_ii2_alisfatura
(muhtur,mturid,muhodm,modmid,uid)
SELECT @mtu,CASE
WHEN @mtu = 3 THEN (SELECT Max(id)
FROM m_ii1_vsf_personel)
WHEN @mtu = 2 THEN (SELECT Max(id)
FROM m_ii1_vsf_firma)
WHEN @mtu = 4 THEN (SELECT Max(id)
FROM m_ii1_vsf_taseron)
END,@mod,@moid,@uid
Note : First two queries will not insert any record if atleast one condition is not matching. But the case statement will insert data will null value even though none of the condition is matched
I am getting wrong MAX(ID) values with this combined code;
...procedure [dbo].[sp_m_ii_2_kaydet]
@mtu int,
@mod int,
@uid int
as
declare @mtuid int
declare @modid int
if (@mtu = 3 and @mod = 1)
set @mtuid = (select MAX(id) from m_ii1_vsf_personel)
set @modid = (select MAX(id) from m_kasa_personel);
if (@mtu = 3 and @mod = 2)
set @mtuid = (select MAX(id) from m_ii1_vsf_personel)
set @modid = (select MAX(id) from m_hesap_personel);
if (@mtu = 3 and @mod = 3)
set @mtuid = (select MAX(id) from m_ii1_vsf_personel)
set @modid = (select MAX(id) from m_senet_personel);
if (@mtu = 3 and @mod = 4)
set @mtuid = (select MAX(id) from m_ii1_vsf_personel)
set @modid = (select MAX(id) from m_cekcikis_personel);
if (@mtu = 2 and @mod = 1)
set @mtuid = (select MAX(id) from m_ii1_vsf_firma)
set @modid = (select MAX(id) from m_kasa_firma);
if (@mtu = 2 and @mod = 2)
set @mtuid = (select MAX(id) from m_ii1_vsf_firma)
set @modid = (select MAX(id) from m_hesap_firma);
if (@mtu = 2 and @mod = 3)
set @mtuid = (select MAX(id) from m_ii1_vsf_firma)
set @modid = (select MAX(id) from m_senet_firma);
if (@mtu = 2 and @mod = 4)
set @mtuid = (select MAX(id) from m_ii1_vsf_firma)
set @modid = (select MAX(id) from m_cekcikis_firma);
if (@mtu = 4 and @mod = 1)
set @mtuid = (select MAX(id) from m_ii1_vsf_taseron)
set @modid = (select MAX(id) from m_kasa_taseron);
if (@mtu = 4 and @mod = 2)
set @mtuid = (select MAX(id) from m_ii1_vsf_taseron)
set @modid = (select MAX(id) from m_hesap_taseron);
if (@mtu = 4 and @mod = 3)
set @mtuid = (select MAX(id) from m_ii1_vsf_taseron)
set @modid = (select MAX(id) from m_senet_taseron);
if (@mtu = 4 and @mod = 4)
set @mtuid = (select MAX(id) from m_ii1_vsf_taseron)
set @modid = (select MAX(id) from m_cekcikis_taseron);
insert into m_ii2_alisfatura (muhtur, mturid, muhodm, modmid, uid) values
(@mtu, @mtuid, @mod, @modid, @uid)
infact you can do that with similar syntax if you are using SQL server 2012 or later version..
Eg:
create table #tt(Id int)
create table #tu(tid int)
insert into #tu values(1),(2)
declare @test int = 1
insert into #tt
values (iif(@test=1,(select min(tid) from #tu),(select max(tid) from #tu)))
select * from #tt
Your query modified
create procedure sp_m_ii_2_kaydet
@mtu int,
@mod int,
@moid int,
@uid int
as
insert into m_ii2_alisfatura
(muhtur, mturid, muhodm, modmid, uid) values
(@mtu,
IIF (@mtu = 3, (SELECT MAX(id) FROM m_ii1_vsf_personel),IIF(@mtu = 2,(SELECT MAX(id) FROM m_ii1_vsf_firma),
IIF(@mtu = 4,( SELECT MAX(id) FROM m_ii1_vsf_taseron),0))),
@mod, @moid, @uid)
