While loop

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  June 22, 2017 at 1:38 am

  #338263

  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.

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  June 22, 2017 at 2:22 am

  #1947690

  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;

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 22, 2017 at 2:46 am

  #1947693

  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

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  June 22, 2017 at 3:22 am

  #1947695

  Thanks.  I like alternate options. 🙂

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 22, 2017 at 6:59 am

  #1947735

  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