Click here to Skip to main content
15,063,556 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi Everyone,
I have CircleId as follows: 1,2,3,...10,11,12,13,14 and so on.
I have FileType as Follows: DBBH,NBH,DBH,BBH
I am totally confused in my Stored procedure.
There are two Condition in my Stored procedure as follows:
First Condition:if i found the number of records greater than equal to 4 for circleID = 14 then it will not allowed to save the record.
Second Condition:if records is already exists return zero.

ALTER PROCEDURE [dbo].[K2_CHECKENTRYINFILELOG] 
@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;	
  select FILETYPE,FILEDATE,CIRCLEID from K2FILELOG with (nolock) GROUP BY CIRCLEID
   having count(*) >= 4
/* where FILEPATH
          [FILENAME]
          FILETYPE = @FILETYPE and	
          FILEDATE = @FILEDATE and	
          CIRCLEID = @CIRCLEID and
          TOTALROWS =@TOTALROWS and
          SUCCESSCOUNT = @SUCCESSCOUNT and
          FAILURECOUNT = @FAILURECOUNT and
          PROCESSED=@PROCESSED and
          ERROR = @ERROR and
          VENDORID = @VENDORID and
          CREATEDBY =  @CREATEDBY and
          CREATEDON =@CREATEDON and
          DELETED = @DELETED	*/
END	

ELSE IF	
@CIRCLEID >= 14 	
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	
Posted
Comments
R Harshal 11-Jul-14 9:08am
   
Please Kindly Guide me.
Thanks
Harshal
W Balboos, GHB 11-Jul-14 9:41am
   
count(*) is not generally good form - choose a specific field;
R Harshal 11-Jul-14 9:52am
   
LETS TAKE COUNT(CIRCLEID)
Kindly revert.
Thanks
HArshal
CHill60 11-Jul-14 10:03am
   
The title of your post does not match the text within it. What is your actual problem?
R Harshal 11-Jul-14 10:09am
   
I WANT TO USE GROUP BY HAVING CLAUSE FOR MY FIRST CONDITION WHICH IS NOT WORKING.
There are two Condition in my Stored procedure as follows:
First Condition:if i found the number of records greater than equal to 4(IF I GET COUNT OF 4 RECORDS) for circleID = 14 then it will not allowed to save the record.
Second Condition:if records is already exists return zero.
R Harshal 11-Jul-14 11:22am
   
Kindly revert.
Thanks Harshal

1 solution

   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900