Professional Web Applications Themes

new proc - Microsoft SQL / MS SQL Server

i am passing in a vi_date and vi_qty. i want the insert to loop for as many times as the vi_qty specifies. thought this would work, but it's not :( at the same time - do a check that if the sql_date is null, assign it the one that i get from the select query. thanks for any assistance. declare n int declare v_date datetime select n = 0 select v_date = vi_date IF v_date is null begin select v_date = MAX(sql_date)+1 from sched_date_dim if rowcount = 0 set v_date = getdate() end while n <= vi_qty begin insert into ...

  1. #1

    Default new proc

    i am passing in a vi_date and vi_qty. i want the insert to loop for as
    many times as the vi_qty specifies. thought this would work, but it's not
    :( at the same time - do a check that if the sql_date is null, assign it
    the one that i get from the select query.

    thanks for any assistance.

    declare n int
    declare v_date datetime
    select n = 0
    select v_date = vi_date
    IF v_date is null
    begin
    select v_date = MAX(sql_date)+1
    from sched_date_dim
    if rowcount = 0
    set v_date = getdate()
    end
    while n <= vi_qty
    begin
    insert into sched_date_dim (sql_date)
    values (v_date)
    select v_date = v_date + 1
    end


    alex Guest

  2. #2

    Default Re: new proc

    [posted and mailed, please reply in news]

    alex ivascu (com) writes: 

    rowcount will always be 1. But if the table is emptym v_date will be
    NULL. You probably mean:

    select v_daet = colaesce(MAX(sql_date), getdate()) + 1

    No wait! Why would you use getdate() and then add one day all the time?
    You probably mean

    select v_daet = colaesce(MAX(sql_date),
    convert(char(8), getdate(), 112)) + 1

    That is, you want the time portion to be midnight.
     

    Since you never change n, this is going to loop forever.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: new proc

    Thanks, Erland. However, for some reason the v_date always start on
    today's date, if I pass in the vi_date as null - and the date_dim table has
    data (which it finds if ran outside the proc). What am I missing? Thanks
    again.

    create procedure sched_load_date_dim (vi_qty int, vi_date datetime)
    as
    declare v_date datetime
    , v_count int
    select v_count=0
    select v_date = vi_date
    if v_date is null
    select v_date = coalesce(MAX(sql_date), getdate()) + 1
    from sched_date_dim
    if rowcount = 0
    select v_date = getdate()
    while v_count <= vi_qty
    begin
    print v_date
    insert into sched_date_dim (sql_date)
    values (v_date)
    select v_date = v_date+1
    select v_count = v_count+1
    end
    go


    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > rowcount will always be 1. But if the table is emptym v_date will be
    > NULL. You probably mean:
    >
    > select v_daet = colaesce(MAX(sql_date), getdate()) + 1
    >
    > No wait! Why would you use getdate() and then add one day all the time?
    > You probably mean
    >
    > select v_daet = colaesce(MAX(sql_date),
    > convert(char(8), getdate(), 112)) + 1
    >
    > That is, you want the time portion to be midnight.

    >
    > Since you never change n, this is going to loop forever.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Alex Guest

  4. #4

    Default Re: new proc

    Alex Ivascu (com) writes: 

    Since the IF statement does not affect any rows, and v_date is NULL,
    rowcount is 0 at this point.

    You would need a BEGIN-END pair.

    But better is remove the test on rowcount, because when you run SELECT,
    rowcount will always be 1. The expression with coalesce takes care of
    the case when the table is empty. SELECT MAX() on an empty table returns
    NULL, and coalesce returns the first of its arguments that has a non-null
    value.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  5. #5

    Default Re: new proc

    Thanks, Erland.


    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > Since the IF statement does not affect any rows, and v_date is NULL,
    > rowcount is 0 at this point.
    >
    > You would need a BEGIN-END pair.
    >
    > But better is remove the test on rowcount, because when you run SELECT,
    > rowcount will always be 1. The expression with coalesce takes care of
    > the case when the table is empty. SELECT MAX() on an empty table returns
    > NULL, and coalesce returns the first of its arguments that has a non-null
    > value.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Alex Guest

Similar Threads

  1. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  2. Proc::ProcessTable
    By Jens Puruckherr in forum PERL Modules
    Replies: 2
    Last Post: August 4th, 11:25 AM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. variable used for proc name
    By John David Adamski in forum Informix
    Replies: 1
    Last Post: September 15th, 05:06 PM
  5. How to 'return' from a Proc?
    By Kero van Gelder in forum Ruby
    Replies: 1
    Last Post: July 7th, 05:08 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139