stored procedure not returning a value

Question

I have created a stored proc that takes a UNC path, appends a sub-folder string based on the date, then executes xp_cmdshell 'Dir ' to check how many files are in the directory.

Unfortunately it doesn't seemt o be returning a value to my called sql.

here is the stored procedure:

create procedure getDirFileCount (
    @sUNCPath Varchar(500),
    @countFiles int OUTPUT
    )

AS
    BEGIN
        declare @files table (ID int IDENTITY, FileName varchar(100)) 
        declare @saveDir varchar(250), @Cmd varchar(500)
        declare @Count int

    -- get the correct path for today's generated files in format \Year\Month\dd.mm.yyyy...     
        set @saveDir =@sUNCPath 
        + cast(YEAR(GETDATE()) as varchar(4)) 
        + '\'  + datename(month, GETDATE()) 
        + '\'+SUBSTRING(CONVERT(CHAR(20), GETDATE(), 101),4, 2) 
        + '.' + LEFT(CONVERT(CHAR(20), GETDATE(), 101), 2)
        + '.' + SUBSTRING(CONVERT(CHAR(20), GETDATE(), 101),7, 4)  

        -- using quoted identifier for the long string UNC path
        set @Cmd= 'dir "' + @saveDir + '" /b'

        insert into @files execute xp_cmdshell @cmd
        select @Count= COUNT(*) from @files     

        SET @countFiles=@Count

        RETURN @countFiles
        --print @countFiles
    END

Here is the code I am using to call the stored proc:

declare @DaysOffset int=0, @I int =0

DECLARE @tmpLetterTbl TABLE (
    [docID] [int] IDENTITY(1,1) NOT NULL,
    [docRef] [varchar](50) NULL,
    [saveDir] [varchar](4000) NULL,
    [totalLettersExpected] [int] NULL,
    [actualLetters] [int] NULL,
    [EQRecordCount] [int] NULL
) 

insert @tmpLetterTbl SELECT distinct docRef, 
    saveDir=max(Save_Directory),
    COUNT(*) as 'Total Letters', null, null
  FROM [alpsMaster].[dbo].[uExtractMonitor]
  where YPTMPID<>0
  group by docRef
  order by 1,2

-- Get the number of rows in the looping table
DECLARE @RowCount INT,@Date varchar(20)

set @Date=rtrim(CONVERT( CHAR(12), getDate()-@DaysOffset, 106)) --'29 Oct 2013'
SET @RowCount = (SELECT COUNT(docID) FROM @tmpLetterTbl) 

WHILE (@I <= @RowCount)
    BEGIN

        DECLARE @docRef VARCHAR(10)
        DECLARE @saveDir VARCHAR(500)
        DECLARE @letterCount int

         -- Get the data from table and set to variables
         SELECT @docRef = docref FROM @tmpLetterTbl WHERE docID = @I
         SELECT @saveDir = saveDir FROM @tmpLetterTbl WHERE docID = @I      

    update @tmpLetterTbl set actualLetters = 0 where docRef=@docRef         
    exec getDirFileCount @SaveDir, @letterCount 

    print cast(@letterCount as varchar(3)) + ' letters in directory: ' + @saveDir

    update @tmpLetterTbl set actualLetters = @letterCount where docID = @I              
    set @I=@I+1

    END

I want the table variable @tmpLetterTbl to be updated with the number of files in the directory returned by the stroed proc getDirFileCount

I get no errors, but @letterCount is not being populated.

What am I doing wrong here please?

thanks Philip

Answer 1

You must also decorate the param with OUTPUT in the call to the procedure:

 exec getDirFileCount @SaveDir, @letterCount OUTPUT