too check record is exists or not,...
if exists(select * from tbl where col1=val1)
begin
select 'Exists'
end
else
begin
select 'Not Exists'
end
so, your sp should like this
ALTER PROCEDURE [dbo].[Usp_UpdateItemInventory]
@NewQty int,
@ItemName varchar(20),
@UserName varchar(20),
AS
BEGIN
declare @ItemId int
declare @startingQty int
declare @MonthId int
declare @monthNm varchar(10)
select @ItemId= Id from HX_Item where Name=@ItemName
if exists(select * from HX_ItemInventory where ItemId=@ItemId)
begin
update HX_ItemInventory set StartingQty= StartingQty+@NewQty,NewShipmentQty=@NewQty
where ItemId=@ItemId
end
else
begin
select @startingQty= StartingQty from HX_ItemInventory where ItemId=@ItemId
select @monthNm= DATENAME(month, getdate()) + '' + Right(Year(getdate()),4)
select @MonthId= Id from HX_Month where MonthName=@monthNm
insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())
end
END
Happy Coding!
:)