Little modifications in your code:
Try this
CREATE procedure gas
(
@action varchar(20),
@serial_no int,
@gas_name varchar(50),
@tran_stat varchar(50)
)
as
begin
select @tran_stat =(CASE WHEN @action='ADD' then 'FR' when @action='Update' then 'FR' when @action='Authorise' then 'AU'
else 'DE' end )
--declare @trannew varchar(10),
if @action='Add'
begin
insert into tbl_Cyl_gas_master(gas_name,tran_stat) values (@gas_name,@tran_stat)
end
else if @action='Update'
begin
if ((@tran_stat = 'FR') AND (@serial_no=@serial_no))
update tbl_Cyl_gas_master set gas_name=@gas_name where serial_no=@serial_no
else
RAISERROR ('Transaction not is fresh',16,1);
end
else if @action='Authorise'
begin
if @tran_stat = 'FR'
update tbl_Cyl_gas_master set tran_stat=@tran_stat where serial_no=@serial_no
else
RAISERROR ('Transaction already Authorised',16,1)
end
else
delete gas_name from tbl_Cyl_gas_master where serial_no=@serial_no
end
GO