如何在一个存储过程中选择和插入查询 [英] how to select and insert query in one stored procedure

问题描述

大家好，



i创建了一个无法正常工作的存储过程。请指导我。

我想从中选择数据Table1并将其插入到同一个表中，即：Table1。如果Table1中存在相同的记录，那么它将返回Zero.Please看看存储过程如下：

Hi Everyone,

i have create a Stored procedure which is not working .Please Guide me.
I want to select data from Table1 and insert it to same table ie:Table1 .If the same record is exist in Table1 then it will return me Zero.Please have a look on Stored Procedure as follows:

CREATE PROCEDURE [dbo].[K2_INSERTFILELOG] 
@FILENAME VARCHAR(150),
@FILEPATH VARCHAR(MAX),
@FILETYPE int,
@FILEDATE datetime,
@CIRCLEID INT,
@TOTALROWS int,
@SUCCESSCOUNT int,
@FAILURECOUNT int,
@PROCESSED bit,
@ERROR varchar(MAX),
@VENDORID int,
@CREATEDBY INT = 1,
@CREATEDON DATETIME,
@DELETED BIT=0,
@FILELOGID bigINT OUT
AS
BEGIN
IF (@PROCESSED = 1)
BEGIN
select FILEPATH, [FILENAME],FILETYPE,FILEDATE,CIRCLEID,TOTALROWS,SUCCESSCOUNT,FAILURECOUNT,PROCESSED,ERROR,VENDORID,CREATEDBY, CREATEDON, DELETED 
                         from K2FILELOG where FILETYPE = @FILETYPE and
                                              FILEDATE = @FILEDATE and
                                              CIRCLEID = @CIRCLEID and
                                              Processed = 1   
END
ELSE
BEGIN
INSERT INTO K2FILELOG
	   (FILEPATH, [FILENAME],FILETYPE,FILEDATE,CIRCLEID,TOTALROWS,SUCCESSCOUNT,FAILURECOUNT,PROCESSED,ERROR,VENDORID,CREATEDBY, CREATEDON, DELETED)
values (@FILEPATH ,@FILENAME,@FILETYPE,@FILEDATE,@CIRCLEID,@TOTALROWS,@SUCCESSCOUNT,@FAILURECOUNT,@PROCESSED,@ERROR ,@VENDORID,@CREATEDBY,@CREATEDON,@DELETED);
END

END

推荐答案

嗨团队，

我得到了解决方案。

Hi Team,
I got the Solution .

ALTER PROCEDURE [dbo].[K2_INSERTFILELOG] 
@FILENAME VARCHAR(150),
@FILEPATH VARCHAR(MAX),
@FILETYPE int,
@FILEDATE datetime,
@CIRCLEID INT,
@TOTALROWS int,
@SUCCESSCOUNT int,
@FAILURECOUNT int,
@PROCESSED bit,
@ERROR varchar(MAX),
@VENDORID int,
@CREATEDBY INT = 1,
@CREATEDON DATETIME,
@DELETED BIT=0,
@FILELOGID bigINT OUT
AS
BEGIN
declare @Count int
select @Count = count(*) 
--FILEPATH, [FILENAME],FILETYPE,FILEDATE,CIRCLEID,TOTALROWS,SUCCESSCOUNT,FAILURECOUNT,PROCESSED,ERROR,VENDORID,CREATEDBY, CREATEDON, DELETED 
                         from K2FILELOG where FILETYPE = @FILETYPE and
                                              FILEDATE = @FILEDATE and
                                              CIRCLEID = @CIRCLEID and
                                              Processed = 1   
IF (@Count = 1)
BEGIN
set @FILELOGID = -1
return;
END
ELSE
BEGIN
INSERT INTO K2FILELOG
	   (FILEPATH, [FILENAME],FILETYPE,FILEDATE,CIRCLEID,TOTALROWS,SUCCESSCOUNT,FAILURECOUNT,PROCESSED,ERROR,VENDORID,CREATEDBY, CREATEDON, DELETED)
values (@FILEPATH ,@FILENAME,@FILETYPE,@FILEDATE,@CIRCLEID,@TOTALROWS,@SUCCESSCOUNT,@FAILURECOUNT,@PROCESSED,@ERROR ,@VENDORID,@CREATEDBY,@CREATEDON,@DELETED);
set @FILELOGID =@@identity
END
END

这篇关于如何在一个存储过程中选择和插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！