Conditional Store Procedure in Sqlserver

Question

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)