在sql中重复记录 [英] Duplicate records in sql

问题描述

我有一个表格，主管，经理和导演aspx表格。当任何用户上传文件时，该文件首先进入supervsior然后是经理，然后是导演批准/拒绝



用户kat上传文件和他/她的文件用于批准/拒绝supervsior，经理，主管，这似乎在表格中

i have a form which is supervisor ,manager and director aspx forms . when any user upload documents then this document first goes to supervsior then manager and then director for approve/reject

user "kat" uplaod document and his/her document goes for approval /reject to supervsior,manager,director and it seems like this in table

Seqno DocID ApproveID ApproveBy DesigID ApproveDate

1        16        1              abc            1           10/11/2013

2        16        1               def           2           11/12/2013

3       16          1            xyzz         3             14/12/2013

当用户kat看文件时文件批准/拒绝它似乎是这样的

when user "kat" see document wheather document approve/reject it seems like this

docname fileuploaded uploadedate deptype status

finad   fina.docx   04/11/2013  finance approve

当另一个用户上传文件时以及此文件批准给supervsior，经理，导演


然后以supervsior形式看起来像这样

when another user upload document and when this document goes for approval to supervsior,manager,director

then in supervsior form it seems like this

docid    docname     filename department      email             uploadedby uploadedate

  16     finad      fina.docx    fiance   [e-mail]        kat        04/11/2013 (this is old document)

   17    hrrr         hrr.docs  finance    [e-mail]         john   15/11/2013  (this is new document

）





所以当supervsior批准文件（docid 17）时，已经批准/拒绝的旧文件也保存在数据库中，之后在表格中显示如下这样

)


so when supervsior approve document (docid 17) then old document which is already approve/reject also save in database and after this in table seems like this

Seqno DocID     ApproveID         ApproveBy   DesigID    ApproveDate

1        16        2              abc            1           10/11/2013

2        16        1               def           2           11/12/2013

3       16          1              xyzz          3             14/12/2013

5       17          3              dfsdf         1             15/11/2013

这里1是批准，3是待定



我使用下拉和下拉我填写下拉列表中的所有值批准，拒绝，等待



所以当katagian看到他/她的文件时它会看到我这样的

下拉 [ ^ ]

here 1 is approve and 3 is pending

"i use dropdown and in dropdown i fill all values in dropdown "approve,reject ,pending"

so when "kat" agian to see his /her document it sees me like this
dropdown[^]

docname fileuploaded uploadedate deptype status

finad   fina.docx   04/11/2013  finance reject

i use sp like this < br $> b $ b

i use sp like this

ALTER procedure [dbo].[approveddd]
@DocID int,
@ApproveID int,     
@ApproveBy nvarchar(50),
@DesigID int
as

IF EXISTS(Select DocID from Approval where DocID=@DocID and ApproveBy=@ApproveBy)
Update Approval set ApproveID=@ApproveID where DocID=@DocID and ApproveBy=@ApproveBy
ELSE
insert Approval (DocID,ApproveID,ApproveBy,DesigID,ApproveDate)
values(@DocID,@ApproveID,@ApproveBy,@DesigID,GETDATE())

推荐答案

您好，



您的存储过程应该是这样的（以粗体添加代码）：

Hi,

Your stored procedure should look something like this (added code in bold):

ALTER procedure [dbo].[approveddd]
@DocID int,
@ApproveID int,     
@ApproveBy nvarchar(50),
@DesigID int
as

IF NOT EXISTS(SELECT 1
		FROM Approval
		WHERE DocID = @DocID
			AND (ApproveID = 1
				OR ApproveID = 2))
   BEGIN
	IF EXISTS(Select DocID from Approval where DocID=@DocID and ApproveBy=@ApproveBy)
	Update Approval set ApproveID=@ApproveID where DocID=@DocID and ApproveBy=@ApproveBy
	ELSE
	insert Approval (DocID,ApproveID,ApproveBy,DesigID,ApproveDate)
	values(@DocID,@ApproveID,@ApproveBy,@DesigID,GETDATE())
   END

这里，我假设ApproveID = 1 - 文件被批准，ApproveID = 2 - 文件被拒绝（你可能想要参数化这个。）

Here, I am assuming that ApproveID = 1 - document is approved, ApproveID = 2 - document is rejected (you may want to parametrize this).

这篇关于在sql中重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！