Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    Unanswered: To avoid looping

    Hi All,

    Very Ad-hoc requirement I am having.

    I've got one table with two columns.
    Column Name Data Type
    1) Id Integer Identity
    2) RemDate DateTime

    I've to write one SP/JOB in that there will be an integer input parameter @numofday.

    Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month.

    This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping.

    Thanks in advance,

  2. #2
    Join Date
    Nov 2002
    You could write 31 IF statements...

  3. #3
    Join Date
    Jun 2003

    Thumbs up Re

    u have to create table which contains values from 0 to 31

    --create tables

    create table #t1
    days int

    create table #yourtable
    Id int Identity(1,1),
    RemDate DateTime

    ------insert 0 to 31 in #t1 table
    declare @i int
    declare @sql varchar(1000)
    set @i=0
    set @sql='insert into #t1 select ' +convert(varchar,@i)
    --print @sql
    set @i=@i+1
    if @i=32 break
    ------------- create procedure to insert values in #yourtable
    create proc remdate_sp @numofday int
    insert into #yourtable (RemDate)
    select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)+days from #t1 where days<datepart(day,dateadd(mm,1,getdate() - day(getdate())))-@numofday
    order by days


    exec remdate_sp 5
    I love cursor,though I never use it.Cos' I dont want to misuse it.


Posting Permissions

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