如何检查表中是否存在值 [英] How to check whether value exists in a table or not
问题描述
我有两个表就像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屋!