Conditional Store Procedure in Sqlserver
Write Your procedure to enhance functionality
First create a Table with following Column
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[product](
[pid] [int] IDENTITY(1,1) NOT NULL,
[productname] [varchar](50) NULL,
[qty] [int] NULL,
CONSTRAINT [pk] PRIMARY KEY CLUSTERED
(
[pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [unq] UNIQUE NONCLUSTERED
(
[productname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[product] WITH CHECK ADD CONSTRAINT [checkqty] CHECK (([qty]>(0)))
GO
ALTER TABLE [dbo].[product] CHECK CONSTRAINT [checkqty]
GO
After creating Table go to editor window
create procedure proins(@pname varchar(50),@qty int,@type char(1))
as
begin
if(exists(select top 1 * from product where productname=@pname) and (@type='p' or @type='P'))
begin
declare @getval as int
select @getval=qty from product where productname=@pname
set @getval=@getval+@qty;
update product set qty=@getval where productname=@pname
end
else if(exists(select top 1 * from product where productname=@pname) and (@type='s' or @type='S'))
begin
declare @getvall as int
select @getvall=qty from product where productname=@pname
declare @tot as int
set @tot=@getvall-@qty;
update product set qty=@tot where productname=@pname
end
else
begin
insert into product values(@pname,@qty)
end
end
exec proins 'apple',9,'s'
select * from product
insert into product values('apple',45)