本文介绍了如何检查表中是否存在值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我有两个表就像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如下: 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 解决方案 检查记录是否存在,... 如果 存在 (选择 * from tbl 其中 col1 = val1 ) 开始 选择 ' 存在' 结束 否则 开始 选择 ' 不存在' end 所以,你的sp应该是这样的 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 如果 存在(选择 * 来自 HX_ItemInventory 其中 ItemId = @ ItemId) 开始 更新 HX_ItemInventory 设置 StartingQty = StartingQty + @ NewQty,NewShipmentQty = @ NewQty 其中 ItemId = @ ItemId end else 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 tblMonthInventorytblMonth contains columns are likeIdMonthNameCreatedByCreatedOn1January2012Chandra2013-02-19 11:52:56.6602February2012Chandra2013-02-19 11:55:31.4203March2012Chandra2013-02-20 15:41:34.500tblMonthInventory ContainsIdMonthIdItemIdStartingQtyValue1 1 1 105 602 1 2 80 143 1 3 150 70here i want to insert one new record to tblMonthinventoryconditions :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)ASBEGIN 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'endelsebegin select 'Not Exists'endso, your sp should like thisALTER PROCEDURE [dbo].[Usp_UpdateItemInventory]@NewQty int,@ItemName varchar(20),@UserName varchar(20),ASBEGIN 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=@ItemIdendelsebegin 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())endENDHappy Coding!:)use If Exist & Transaction inside your proc---declare proc here--declare input /outtput parameterif exists(select id from tblMonth where id=@id)begininsert into tblMonthInventory values(@monthid,@itemid,@startingqty,@value)endelsebeginBEGIN TRANSACTION tranBEGIN TRYinsert into tblMonth values(@id,@monthName,@createddate,@createdon)insert into tblMonthInventory values(@monthid,@itemid,@startingqty,@value)COMMIT TRANSACTION tranEND TRYBEGIN CATCHROLLBACK TRANSACTION tran-- return exception hereEND CATCHend 这篇关于如何检查表中是否存在值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!