CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
song josh
song josh

Reputation: 1

Stored Procedure, find the duplicate file name and return the value

I have logic to verify whether the filename is unique or not. For this I need to pass the parameter 'FileName'

Logic: 

SELECT value = COUNT(*)  
FROM  ProcessingLog
WHERE ProcessingOutcome IN (1,2)  
AND FileName = [FileName] 

IF value <> 0 THEN  
RETURN ErrorID 2

Default: n/a
Error Code: 2: E002: Source File has been previously processed (duplicate Source File)
Output Target: ProcessingLog.ErrorID
Output Format: int

I wrote a query like this:

CREATE PROCEDURE [dbo].[xx]    
    @FileName int = NULL OUTPUT    
AS
BEGIN       
    DECLARE @V_FileName VARCHAR(225);

    SET NOCOUNT ON;

    SELECT FileName, count(*)
    FROM  PbrcServiceProcessingLog  
    WHERE ProcessingOutcome IN (1,2)  
          AND SourceFileName = @SourceFileName 
    GROUP BY SourceFileName
    HAVING COUNT(*) > 0

    IF count !=0 THEN  RETURN ErrorID 2     
END

How can I pass the errorID into the processing ?

Upvotes: 0

Views: 355

Answers (1)

Dale K
Dale K

Reputation: 27449

The following will do what you require.

  1. Your parameter name does not need to be an output
  2. Your parameter name does need to match the parameter used in the query.
  3. A simple exists is all that is required in this case - no counting needed.
CREATE PROCEDURE [dbo].[xx]    
    @FileName int = NULL
AS
BEGIN       
    SET NOCOUNT ON;

    if exists (
        SELECT 1
        FROM PbrcServiceProcessingLog  
        WHERE ProcessingOutcome IN (1,2) AND SourceFileName = @FileName 
    )
      return 2
    else
      return 0;
END

And to insert into a table

declare @FileName nvarchar(256) = 'MyTestFileName.txt', @Result int;

exec @Result = dbo.xx @FileName;

insert into ProcessingLog (ErrorId, FileName)
select @Result, @FileName
where @Result = 2;

Note: This doesn't really require a Stored Procedure, if you want to reuse this query in multiple places put it in an Inline Table Valued Function e.g.

CREATE FUNCTION [dbo].[xx]
(    
  @FileName int = NULL
)
returns table
return
  select case when exists (
    SELECT 1
    FROM PbrcServiceProcessingLog  
    WHERE ProcessingOutcome IN (1,2) AND SourceFileName = @FileName 
  )
  then 2 else 0 end FileExists;

Then for a single value use as:

select FileExists from dbo.xx(@FileName);

Or to call against a column in a table, use as:

select Filename
  , (select FileExists from dbo.xx(FileName)) FileExists
from MyTable;

And to insert into a table

insert into ProcessingLog (ErrorId, FileName)
select ErrorId, @FileName
from (
    select FileExists from dbo.xx(@FileName)
) x
where x.FileExists = 2;

Upvotes: 1

Related Questions