While loop

  • Hey,

    We do this in a Word template so if the file already exists, it increments the filename:
        'Creating the File Name
        DocName$ = Format(Now(), "yyyy-mm-dd") & "-" & IRN$ & "-" & DocTitle$ & ".doc"
        DocNameOnlyNoSuffix$ = Format(Now(), "yyyy-mm-dd") & "-" & IRN$ & "-" & DocTitle$
        FileName$ = Directory$ & "\" & DocName$
       
        'Check if the file is present if not continue
        CheckFilePresent$ = Dir(FileName$)
        LoopCnt = 1
         
        While Len(CheckFilePresent$) <> 0
            CheckFilePresent$ = Dir(Directory$ & "\" & DocNameOnlyNoSuffix$ & LoopCnt & ".doc")
            FileName$ = Directory$ & "\" & DocNameOnlyNoSuffix$ & LoopCnt & ".doc"
            LoopCnt = LoopCnt + 1
        Wend
     

    I can't think how to replicate this in SQL.  Rather than checking for the existence of a file in the filesystem, it would be a column in a table.

    Thanks.

  • I've been racking my brains for days on this, and as soon as I post up, I think I work it out.

    Can this be improved?

    declare @i int = 1;
    declare @DOCNAME nvarchar(254) = 'Complete physical case file [part 1]';
    declare @ATTACHMENTNAME nvarchar(254) = @DOCNAME;

    if exists( select  ATTACHMENTNAME from  CASEFILESCAN where  ATTACHMENTNAME = @ATTACHMENTNAME)begin

    if exists
    (
     select  ATTACHMENTNAME
     from  CASEFILESCAN
     where  ATTACHMENTNAME = @ATTACHMENTNAME
    )
    begin

     while exists (  select  ATTACHMENTNAME  from  CASEFILESCAN  where  ATTACHMENTNAME = @ATTACHMENTNAME ) begin  set @i += 1;  set @ATTACHMENTNAME = @DOCNAME + ' (' + cast(@i as varchar) + ')'; endendprint @ATTACHMENTNAME;

     while exists
     (
      select  ATTACHMENTNAME
      from  CASEFILESCAN
      where  ATTACHMENTNAME = @ATTACHMENTNAME
     )
     begin
      set @i += 1;
      set @ATTACHMENTNAME = @DOCNAME + ' (' + cast(@i as varchar) + ')';
     end
    end
    print @ATTACHMENTNAME;

  • You can avoid a loop by using a Numbers (or Tally) table.  You can create your own table, or spin it up on the fly as I've done below.  This one goes from 1 to 100: if you have more attachments than that, you'll need to expand the table with more numbers and possibly change varchar(3) to varchar(4) or higher.

    WITH N10 AS (
        SELECT n
        FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
        )
    , N100 AS (
        SELECT n1.n
        FROM N10 n1
        CROSS JOIN N10 n2
        )
    , Numbers AS (
        SELECT ROW_NUMBER() OVER (ORDER BY n) AS n
        FROM N100
        )
    SELECT @AttachmentName = CAST(MIN(n) AS varchar(3)) + @DocName
    FROM Numbers Nbr
    LEFT JOIN CaseFileScan c
    ON LEFT(c.AttachmentName,LEN(c.AttachmentName)-LEN(@DocName)) = CAST(Nbr.n AS varchar(3))
    WHERE c.AttachmentName IS NULL

    John

  • Thanks.  I like alternate options. 🙂

  • Something like this might work. If I understand correctly.

    DECLARE --Parameters?
       @irn nvarchar(254),
       @DocTitle nvarchar(254),
       @Directory nvarchar(254);

    DECLARE @DocName nvarchar(254) = CONVERT( char(10), GETDATE(), 120) + '-' + @irn + '-' + @DocTitle;

    SELECT @DocName += ISNULL(NULLIF((SELECT COUNT(*) FROM CASEFILESCAN
                                      WHERE ATTACHMENTNAME LIKE @DocName + '%'
                                      /*AND DIRECTORY = @Directory*/), 0), '') + '.doc';

    Note that if @irn or @DocTitle are NULL, then @DocName will become NULL.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply