Very Computer

Howdy, all.  Pardon my denseness, apparently I don't know Dynamic SQL
as well as I should.

What I'm trying to do:
Run a stored procedure that, given a table name, will find the
previous Friday's data and stuff it back into the same table for
Saturday and Sunday.

Every table has an asof_date, which is the date the information is
from.

My current code is the following.  The issue I'm running into is that
when I try to start adding variables (i.e. changing getdate() into a
variable so I can run it on Tuesday instead of Monday), things get
rapidly out of control.

The goal is to make this an SP so I can say "exec FridaySP
'mikes_lousy_db'" and have it find the previous friday's data, stuff
Saturday's/Sunday's, and quit.
I know I'm going about it the wrong way, especially after seeing
Erland's page on Dynamic SP, so hopefully someone can help.  Thanks.

--grab Friday's data

        ' where asof_date = convert(char(10), getdate()-3,101)')

--update the asof_date to be Saturday's
update ##FridayInfo
        set asof_date = convert(char(10),getdate()-2,101),
            input_date = getdate()

--insert the updated records

--update the date to be Sunday, now.
update ##FridayInfo set asof_date = convert(char(10),getdate()-2,101)

--insert the updated records

drop table ##FridayInfo

Many thanks!
Michael

Which more precisely means?

I am sorry, but as I don't fully understand what you are doing, and you
neither specify where things go wrong, I cannot really come with any
advice. The only thing that strikes me is this:

Quote:> --update the date to be Sunday, now.
> update ##FridayInfo set asof_date = convert(char(10),getdate()-2,101)

Shouldn't that be -1 rather than -2?

I would also suggest using 112 and char(8) rather than 101, to protect
you for surprises from SET DATEFORMAT, but that is not likely to be your
problem.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

If I could use a variable instead of getdate(), I could tell the SP
(once I turn this into an SP) which date to run for.

An example: I'd like to run this code on Monday.  (The actual files
don't get delivered until Monday).  However, this coming week, the
files won't be delivered until Tuesday, since Monday is a US holiday.
So I'd have to run it Tuesday.  However, since I have used getdate()
for my calculations, it would take Saturday's data and try and fill
Sunday's/Monday's.  In order for it to run correctly, I'd have to
change the code, which obviously is contrary to the whole idea of a
Stored Procedure.  (I realize it's not a stored procedure yet, but
that's my aim)

I looked at your Dynamic SQL page, and tried to emulate it, but kept
running into problems.

However, once I figured out my error, I was able to change my code
from this:

to

Quote:> Shouldn't that be -1 rather than -2?

(yes, my mistake)

Quote:> I would also suggest using 112 and char(8) rather than 101, to protect
> you for surprises from SET DATEFORMAT, but that is not likely to be your
> problem.

Good advice.  I'll do that also.

Thanks again!

Quote:>> What I'm trying to do: Run a stored procedure that, given a table

name, will find the previous Friday's data and stuff it back into the
same table for Saturday and Sunday. <<

That is not WHAT what you are trying to do; that is HOW you are going
to do it,

What you are trying to do is insert a copy of Friday's data with the
dates for the following Saturday and Sunday in a table.  Do not do
this with dynamic SQL; it is waste of resources, slow and dangerous.
If you want to insert data, then use an INSERT INTO statement.
Logical, unh?

Even tho you failed to show us any DDL, we know that if you are
duplicating all the columns except this date column, then this date is
part of the (primary) key.  If your tables do not have real keys, then
you are screwed up and we cannot save you.

INSERT INTO Foobar (keycol, a, b, c, .., foodate, ..)
SELECT keycol, a, b, c, ..
       DATEADD(DD, seq, foodate) AS foo_date, -- compute saturday &
sunday
        ..
  FROM Foobar AS F1,
       Sequence AS S1 -- see notes below
 WHERE S1.seq IN (1,2)
   AND DATEPART(WD, Foobar.foo_date) = 6 -- get all the fridays
   AND NOT EXISTS -- see that saturday & sunday are new
       (SELECT *
          FROM Foobar AS F2
         WHERE F2.keycol = F1.keycol
           AND F2.foo_date = DATEADD(DD, seq, foodate));

Sequence is a table with integer 1 to (n) in it, which you should have
created at the start of the project.  It is a standard programming
trick., like creating a calendar table.

If you want to do this on more than one table, then write an INSERT of
each of them.

Quote:>> > things get rapidly out of control.

>> Which more precisely means?

> If I could use a variable instead of getdate(), I could tell the SP
> (once I turn this into an SP) which date to run for.

I was about to remark that the use of getdate() seemed a little unsafe,
because things could wrong if they were executed on the wrong side of
midnight, but I assumed that you know what you were doing. Little could
I guess, you wanted to sneak in a variable there...

Matter of taste of course, but I tend to prefer to declare a new variable,
to make the statement where the date is used a little cleaner.

Even better is of course is to have a better data model, so you don't
have to do this...

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp