How to check whether value exists in a table or not

Question

0.00/5 (No votes)

See more:

Hi,
I have two tables are like tblMonth and tblMonthInventory
tblMonth contains columns are like

Id	MonthName	CreatedBy	CreatedOn
1	January2012	Chandra	2013-02-19 11:52:56.660
2	February2012	Chandra	2013-02-19 11:55:31.420
3	March2012	Chandra	2013-02-20 15:41:34.500

tblMonthInventory Contains

Id	MonthId	ItemId	StartingQty	Value		
1	   1	   1	   105		  60	 
2	   1	   2	   80		  14	 
3	   1	   3	    150		  70

here i want to insert one new record to tblMonthinventory
conditions :
1.check tblmonth if month id is exists insert record into tblMonthInventory else create month id in tblMonth and then insert record into tblMonthInventory.

please give sample code to do above functionality in stored procedure.

i have sample sp is as follows:

SQL

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
  
  update HX_ItemInventory  set StartingQty= StartingQty+@NewQty,NewShipmentQty=@NewQty
  where ItemId=@ItemId
  
  select @startingQty= StartingQty from HX_ItemInventory where ItemId=@ItemId
  select @monthNm= DATENAME(month, getdate()) + '' + Right(Year(getdate()),4)
-- here i need to check the above condition
  select @MonthId= Id from HX_Month where MonthName=@monthNm
  --Audit record for Item Inventory.
  insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())
END

Posted 2-Apr-13 20:19pm

Madhugundi

Updated 2-Apr-13 20:29pm

Maciej Los

v3

Add a Solution

Comments

Bernhard Hiller 3-Apr-13 2:46am

Not the point of your present problem, but of your future problems: that column "MonthName"... Get rid of it. Store e.g. month and year instead of that (two columns). Later you'll have to create reports on the tables, and year/month will be an important item.

2 solutions

Solution 2

use If Exist & Transaction inside your proc

---declare proc here
--declare input /outtput parameter

if exists(select id from tblMonth where id=@id)
begin
insert into tblMonthInventory values(@monthid,@itemid,@startingqty,@value)
end
else
begin
BEGIN TRANSACTION tran
BEGIN TRY
insert into tblMonth values(@id,@monthName,@createddate,@createdon)
insert into tblMonthInventory values(@monthid,@itemid,@startingqty,@value)
COMMIT TRANSACTION tran
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION tran
-- return exception here
END CATCH
end

Posted 2-Apr-13 21:03pm

Anupkumar7186

Comments

Madhugundi 3-Apr-13 3:09am

thank you consider my post please check the following code i am getting errors
if exists( select @MonthId= Id from HX_Month where MonthName=@monthNm)
begin
insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())
end
else
begin
insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())
end

Msg 102, Level 15, State 1, Procedure Usp_UpdateItemInventory, Line 24
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Procedure Usp_UpdateItemInventory, Line 28
Incorrect syntax near the keyword 'else'.

Madhugundi 3-Apr-13 3:09am

after @MonthId=

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-04-02T20:49:00

Solution 1

too check record is exists or not,...

SQL

if exists(select * from tbl where col1=val1) 
begin
    select 'Exists'
end
else
begin
    select 'Not Exists'
end

so, your sp should like this

SQL

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)
-- here i need to check the above condition
  select @MonthId= Id from HX_Month where MonthName=@monthNm
  --Audit record for Item Inventory.
  insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())
end
END

Happy Coding!
:)

Posted 2-Apr-13 20:49pm

Aarti Meswania

Updated 2-Apr-13 20:57pm

v3

Comments

Madhugundi 3-Apr-13 3:05am

thank you consider my post please check the following code i am getting errors
if exists( select @MonthId= Id from HX_Month where MonthName=@monthNm)
begin
insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())
end
else
begin
insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())
end

Msg 102, Level 15, State 1, Procedure Usp_UpdateItemInventory, Line 24
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Procedure Usp_UpdateItemInventory, Line 28
Incorrect syntax near the keyword 'else'.

Madhugundi 3-Apr-13 3:06am

after @MonthId=

Aarti Meswania 3-Apr-13 3:09am

if exists( select @MonthId= Id from HX_Month where MonthName=@monthNm)

this line should be
if exists( select Id from HX_Month where MonthName=@monthNm)
you can't set value in @MonthId while query is inside "Exists()" finction

Madhugundi 3-Apr-13 3:11am

i need to capture monthId in local variable to insert record in tblMonthInventory

Aarti Meswania 3-Apr-13 3:13am

use sp I have posted in answer
I think you need if exists for decide when to fire update query and when insert

Madhugundi 3-Apr-13 3:12am

then how to get monthId to insert query
insert into HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn ) values(@MonthId,@ItemId,@startingQty,@NewQty,@UserName,GETDATE())