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

    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,
    Jai

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

  3. #3
    Join Date
    Jun 2003
    Posts
    269

    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
    while(@i)>-1
    begin
    set @sql='insert into #t1 select ' +convert(varchar,@i)
    --print @sql
    exec(@sql)
    set @i=@i+1
    if @i=32 break
    end
    ------------- create procedure to insert values in #yourtable
    create proc remdate_sp @numofday int
    as
    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.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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