如何检查存储过程中的记录。 [英] How to check the record in Stored procedure.

问题描述

大家好，

我的存储过程如下：

我想在我的一个存储过程中执行三个条件。



第一个条件：在文件日志表中插入新记录



第二个条件：如果记录已经在Filelog表中以相同的FileDate，FileType退出，CircleId和处理为1然后返回零。



第三个条件：如果CircleId大于等于14，则不允许保存记录。



从这三个条件，我的第一个条件和第二个条件正在工作。第三个条件不起作用。请指导我同样。

我的存储程序如下：

Hi Everyone,
I have the Stored Procedure as follows:
I want to perform three condition in my one stored procedure.

First Condition:Insert the New Record in Filelog Table

Second Condition:If Record is already exits in Filelog Table with same FileDate,FileType,CircleId and processed is 1 then return zero.

Third Condition:If CircleId is greater than equal to 14 then don't allowed to save the Record.

From these Three condition, my First condition and second condition is working .Third Condition is not working.Please Guide Me For the Same.
My Stored Procedure is as follows:

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(*) 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

-- Third Condition

IF
@CIRCLEID >= 14 and
@PROCESSED = 1

BEGIN
SET @FILELOGID = (SELECT ID FROM  K2FILELOG WITH (NOLOCK))
RETURN @FILELOGID
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

在DataManager类中，我编写了以下代码：

In DataManager class I have Written the Code AS follows :

public int AddFileLogRecords(IEntityBase entityBase)
        {
            int rowsAffected = 0;
            try
            {
                Logger.Write("Inside Kaizen2GDataManager.AddFileLogRecords()", LogType.Information);
                IFileLog fileLog = entityBase as IFileLog;
                /*if (fileLog.CircleId >= 14)
                {
                    //// not to save the Record
                }
                else
                {*/
               CommonDataAccess.CreateStoredProcCommandWrapper(KaizenConstants.SPK2INSERTFILELOG);
                //// }
                CommonDataAccess.AddInParameter(KaizenConstants.FILEPATH, DbType.String, fileLog.FilePath);
                CommonDataAccess.AddInParameter(KaizenConstants.FILENAME, DbType.String, fileLog.FileName);
                CommonDataAccess.AddInParameter(KaizenConstants.FILETYPE, DbType.Int32, fileLog.FileType);
                CommonDataAccess.AddInParameter(KaizenConstants.FILEDATE, DbType.DateTime, fileLog.FileDate);
                CommonDataAccess.AddInParameter(KaizenConstants.CIRCLEID, DbType.Int32, fileLog.CircleId);
                CommonDataAccess.AddInParameter(KaizenConstants.TOTALROWS, DbType.Int32, fileLog.TotalRows);
                CommonDataAccess.AddInParameter(KaizenConstants.SUCCESSCOUNT, DbType.Int32, fileLog.SuccessCount);
                CommonDataAccess.AddInParameter(KaizenConstants.FAILURECOUNT, DbType.Int32, fileLog.FailureCount);
                CommonDataAccess.AddInParameter(KaizenConstants.ERROR, DbType.String, fileLog.ErrorMessage);
                CommonDataAccess.AddInParameter(KaizenConstants.PROCESSED, DbType.Boolean, fileLog.Processed);
                CommonDataAccess.AddInParameter(KaizenConstants.VENDORID, DbType.Int32, fileLog.VendorId);
                CommonDataAccess.AddInParameter(KaizenConstants.CREATEDBY, DbType.Int32, 1);
                CommonDataAccess.AddInParameter(KaizenConstants.CREATEDON, DbType.DateTime, DateTime.Now.ObjectToDBDateTime());
                CommonDataAccess.AddInParameter(KaizenConstants.DELETED, DbType.Boolean, 0);
                CommonDataAccess.AddInParameter(KaizenConstants.FILELOGID, DbType.Int32, fileLog.FileLogId);
                rowsAffected = CommonDataAccess.ExceuteNonQuery();
                //// fileLog.FileLogId = CommonDataAccess.GetParameterValue(KaizenConstants.FILELOGID).ObjectToInt32();
                Logger.Write("Exiting Kaizen2GDataManager.AddFileLogRecords()", LogType.Information);
            }
            catch (Exception exception)
            {
                throw exception;
            }

            return rowsAffected;
        }

推荐答案

您的测试是

IF @CIRCLEID >= 14 and @PROCESSED = 1

@PROCESSED 传递的值为 fileLog.Processed ，其类型为 DbType.Boolean

我认为这里的问题可能是 true 被定义为非-zero值，你不应该依赖它等于值 1



在datamanager类中，尝试使用

@PROCESSED is passed in the value of fileLog.Processed which is of type DbType.Boolean
I think the problem here might be that true is defined as a non-zero value, you shouldn't rely on it being equal to the value 1

In your datamanager class try this line instead

CommonDataAccess.AddInParameter(KaizenConstants.PROCESSED, DbType.Boolean, (fileLog.Processed) ? 1 : 0);

强制值1 int o如果fileLog.Processed为真SP，否则为0



或者......



转动 IF sql中的语句从if true变为if not false

which forces the value 1 into the SP if fileLog.Processed is true otherwise 0

OR ...

Turn the IF statement in the sql from "if true" to "if not false"

IF @CIRCLEID >= 14 and @PROCESSED <> 0

这篇关于如何检查存储过程中的记录。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！