TSQL While statement problem

Results 1 to 2 of 2

Thread: TSQL While statement problem

  1. #1
    Join Date
    Dec 1969

    Default TSQL While statement problem

    Well I am doing something very simple(i did it in asp in 15 minutes) I want to get 2 dates(got that part) and create a temp table with a number and a date. I have trouble generating all the dates. This is the first step in my big report. The while statement won&#039t execute though it should in my opinion.<BR><BR>CREATE PROCEDURE sp_report <BR> @connum varchar(20),<BR> @firstdate varchar(10),<BR> @seconddate varchar(10)<BR><BR>AS<BR><BR>DECLARE @connum2 varchar(20), @dateformonth varchar(10), @counter numeric<BR><BR>DECLARE contractor_cursor CURSOR FOR <BR>SELECT contractornum FROM contractors WHERE contractornum=@connum<BR><BR>OPEN contractor_cursor<BR><BR>FETCH NEXT FROM contractor_cursor INTO @connum2<BR><BR>CLOSE contractor_cursor<BR>DEALLOCATE contractor_cursor<BR><BR> select @dateformonth=@firstdate<BR><BR> INSERT INTO templogs (connum,date) VALUES (@connum2,@dateformonth)<BR> <BR> never executes--&#062;while (@dateformonth&#060;&#062;@seconddate)<BR> begin<BR> set @dateformonth=dateadd(d,1,@dateformonth)<BR><BR> INSERT INTO templogs (connum,date) VALUES (@connum2,@dateformonth)<BR> end<BR><BR>Matt

  2. #2
    Garth Guest

    Default RE: TSQL While statement problem

    I am not sure the problem is with your WHILE. Execute the code as a batch (not a procedure) in Query Analyzer to see which line is causing the problem. As an FYI, the code shown below demonstrates how to WHILE through some dates.<BR><BR>Although you did not ask for help with your cursor code, I would be remorse if I didn&#039t offer to help with the programming logic. There are some good reasons to use cursors, but this may not be one of them.<BR><BR>Garth<BR>www.SQLBook.com<BR><BR>--SQL Script<BR>DECLARE @Date1 smalldatetime,<BR> @Date2 smalldatetime<BR><BR>SET @Date1 = &#039 12/10/00&#039<BR>SET @Date2 = &#039 12/20/00&#039<BR><BR><BR>WHILE (@Date1 &#060; @Date2)<BR> BEGIN<BR> PRINT @Date1<BR> SET @Date1 = DATEADD(day,1,@Date1)<BR> END<BR><BR>--Results--<BR><BR>Dec 10 2000 12:00AM<BR>Dec 11 2000 12:00AM<BR>Dec 12 2000 12:00AM<BR>Dec 13 2000 12:00AM<BR>Dec 14 2000 12:00AM<BR>Dec 15 2000 12:00AM<BR>Dec 16 2000 12:00AM<BR>Dec 17 2000 12:00AM<BR>Dec 18 2000 12:00AM<BR>Dec 19 2000 12:00AM<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts