如何知道ID是否存在? [英] How to know if ID is Exist ?

问题描述

人问.
如何知道RecID是否存在于表中，如果是，则更新ITEM的最新提醒备注，如果否，则将状态更新为1 b>，继续将状态为1的项目插入表中.

实际上我已经创建了下面的SP插入，但是更新是我的问题，我的下面的STATUS默认为1:

谢谢:)

  set   ANSI_NULLS   ON 
设置  QUOTED_IDENTIFIER  >打开
开始

更改 过程 [dbo].[sp_Insert_ITEM_REMINDER]
(
 @ claimno   nvarchar ( 16 )，
 @备注  nvarchar ( 225 )，
 @ claimtype   nvarchar ( 10 )，
 @ 修改的 nvarchar ( 255 )，
 @状态  int ，
 @ recid   int  输出
) AS 

开始
设置 没有计数 打开
- 插入类型代码-
插入  INTO  dbo.[EP_ADMIN_ITEM_REMINDER]
(
[ClaimNo]，[备注]，[ClaimType]，[ModifiedBy]，[状态]
)
值
(
 @ claimno ， @备注， @ claimtype  ， @由修改， @ status 
)

选择  @ recid  =  SCOPE_IDENTITY ()
 END  

解决方案

请参考 ^ ]:
它基于带有源表的join 的结果在目标表上执行插入，更新或删除操作.

看一下这个查询以供参考；您可以从中获得一些帮助来解决您的查询:

  MERGE 
 INTO  table2 t2
使用 table1 t1
打开 t2.email = t1.email
时间 匹配 时间
更新
 SET  t2.col1 = t1.col1，
        t2.col2 = t1.col2
何时 不 已匹配 插入(col1，col2)
值(t1.col1，t1.col2)

设计错误.该ID应该由数据库而不是客户端创建.并且客户端应该以某种方式知道数据是新数据还是更新数据，然后调用适当的函数(可以是相同的存储过程，当数据为新数据时，ID为null).

Guys Question .
How to know if the RecID is Existing to the TAble, If YES, UPDATE The latest reminder REMARKS for the ITEM and Update the STATUS to 1 if NO, Proceed to Insert the item to the table with status equal to 1.

Actually i crated already the SP insert below but update is my problem my STATUS below is default = 1 :

Thank you :)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Insert_ITEM_REMINDER]
(
	@claimno nvarchar(16),
	@remarks nvarchar(225),
	@claimtype nvarchar(10),
	@modifiedby  nvarchar(255),
	@status int,
	@recid int OUTPUT 
)AS

BEGIN
		SET NOCOUNT ON
		-- INSERT the type code --
		INSERT INTO dbo.[EP_ADMIN_ITEM_REMINDER]
		(	
			[ClaimNo],[Remarks],[ClaimType],[ModifiedBy],[Status]
		) 
		VALUES
		(
			@claimno,@remarks,@claimtype,@modifiedby,@status
		)	
		
		SELECT  @recid = SCOPE_IDENTITY()		
END

解决方案

Refer MERGE (Transact-SQL)[^] :
It Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

Have a look on this query for reference; you can get some help from it to solve you query:

MERGE
INTO    table2 t2
USING   table1 t1
ON      t2.email = t1.email
WHEN MATCHED THEN
UPDATE
SET     t2.col1 = t1.col1,
        t2.col2 = t1.col2
WHEN NOT MATCHED THEN
INSERT  (col1, col2)
VALUES  (t1.col1, t1.col2)

The design is wrong. The ID should be created by the database, not by the client. And the client should know somehow if the data is new or updated, and then call an appropriate function (could be the same stored procedure, with null for the ID when the data are new).

这篇关于如何知道ID是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！