如何检查表中是否存在值 [英] How to check whether value exists in a table or not

查看:78
本文介绍了如何检查表中是否存在值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我有两个表就像tblMonth和tblMonthInventory

tblMonth包含的列类似于

 Id MonthName CreatedBy CreatedOn 
2012年1月1日Chandra 2013-02-19 11:52:56.660
2 2012年2月Chandra 2013-02-19 11:55:31.420
3月2012 Chandra 2013-02- 20 15:41:34.500



tblMonthInventory包含

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



这里我要向tblMonthinventory插入一条新记录

条件:

1.check tblmonth如果存在月份id,则将记录插入tblMonthInventory,然后在tblMonth中创建月份ID,然后将记录插入tblMonthInventory。



请提供示例代码以在存储过程中执行上述功能。



i有样本sp如下:



 < span class =code-keyword> ALTER   PROCEDURE  [dbo]。[Usp_UpdateItemInventory] ​​
@NewQty int
@ ItemName varchar 20 ),
@ UserName varchar 20
AS
BEGIN
声明 @ ItemId int
声明 @ startingQty int
声明 @ MonthId int
声明 @ monthNm varchar 10

选择 @ ItemId = Id 来自 HX_Item 其中名称= @ ItemName

update HX_ItemInventory set StartingQty = StartingQty + @ NewQty,NewShipmentQty = @ NewQty
其中 ItemId = @ ItemId

选择 @ startingQty = StartingQty 来自 HX_ItemInventory 其中​​ ItemId = @ ItemId
选择 @ monthNm = DATENAME(月,getdate())+ ' ' + 正确(年(getdate()), 4
- 这里我需要检查以上条件
选择 @ MonthId = Id 来自 HX_Month 其中 MonthName = @ monthNm
- 项目库存的审核记录。
insert 进入 HX_MonthInventory(MonthId,ItemId,StartingQty,NewShipmentQty,CreatedBy,CreatedOn) values @MonthId @ ItemId @ startingQty @ NewQty @ UserName ,GETDATE())
END

解决方案

检查记录是否存在,...

 如果  存在 选择 * < span class =code-keyword> from   tbl  其中  col1 = val1 
开始
选择 ' 存在'
结束
否则
开始
选择 ' 不存在'
end



所以,你的sp应该是这样的

  ALTER   PROCEDURE  [dbo]。[Usp_UpdateItemInventory] ​​
@ NewQty int
@ ItemName varchar 20 ),
@ UserName varchar 20 ),
< span class =code-keyword> AS
BEGIN
声明 @ ItemId int
声明 @ startingQty int
声明 @ MonthId int
声明 @ monthNm varchar 10

选择 @ ItemId = Id 来自 HX_Item 其中名称= @ ItemName
如果 存在选择 * 来自 HX_ItemInventory 其中 ItemId = @ ItemId)
开始

更新 HX_ItemInventory 设置 StartingQty = StartingQty + @ NewQty,NewShipmentQty = @ NewQty
其中 ItemId = @ ItemId
end
else
< span class =code-keyword> begin

select @ startingQty = StartingQty 来自 HX_ItemInventory 其中 ItemId = @ ItemId
选择 @ monthNm = DATENAME(月,getdate())+ ' ' + 正确(年份(getdate( )), 4
- 这里我需要检查以上条件
选择 @ MonthId = Id 来自 HX_Month 其中 MonthName = @ monthNm
- 审核商品库存记录。
插入 进入 HX_MonthInventory(MonthId,ItemId,StartingQty) ,NewShipmentQty,CreatedBy,CreatedOn) @ MonthId @ItemId @ startingQty @ NewQty @UserName ,GETD ATE())
end
END



快乐编码!

:)


使用如果你的proc中有Exist&Transaction





---在这里声明proc

--declare输入/输出参数



如果存在(从tblMonth中选择id,其中id = @id)

begin

插入tblMonthInventory值(@ monthid,@ itemid,@ startingqty ,@ value)

end

else

begin

BEGIN TRANSACTION tran

开始尝试

插入tblMonth值(@ id,@ monthName,@ createddate,@ createdon)

插入到tblMonthInventory值(@ monthid,@ itemid,@ startingqty,@价值)

COMMIT TRANSACTION tran

结束尝试



BEGIN CATCH

ROLLBACK TRANSACTION tran

- 这里返回异常

END CATCH

end

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:

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

解决方案

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)
-- 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!
:)


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


这篇关于如何检查表中是否存在值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆