SQL Help

AsSiMiLaTeD

I have a script I'm writing in SQL Server 2005, and need a little help with a specific part.

I don't need to get into the details of the script, but at a very high level:

At the very top I declare and set a start and end date, like so:

DECLARE @start datetime
DECLARE @end datetime
SET @start = '01/14/2008 00:00:00 AM'
SET @end = '01/14/2008 11:59:59 PM'

Then I just call the start and end variables from within the script and do stuff, that's not my question though.

What I need help with is creating a loop or something that can loop through the dates. Let's say we have a table or array of 90 dates and an 'autonumber' type field that's incremented sequentially at each date, like so:

1 12/01/2007 12/02/2007
2 12/02/2007 12/03/2007
3 12/03/2007 12/04/2007
etc

Then we just need a statement to loop through the days. I'm not a developer, but simplistically, something that basically starts at autonumber 1, grabs the first date and stamps it as the StartDate in the script, grabs the second date and stamps it as the EndDate, then runs the script, then increments the autonumber by one and starts again, until it reaches the end of the table.

Can that be done in SQL, or do we need to use VB to do that and call the script?

Thanks

Sami

Very easy to do in code, not so sure how SQL works though as I do everything in C++ or C#.

vlam

I have not done SQL for a while but this a classic example of not to use loop in SQL SP.

You should use join. The table of the 90 dates should be join in your selection criteria. In SQL, you don't want to do 90 interations of the select.
You got the first part already by having a separate tables with your selection criteria, use it correctly.

If you are totally lost and still need help, PM me. I'll get my SQL going again but what you are looking to do isn't very diffifcult you realize the concept.

AsSiMiLaTeD

The trick is I need to run the whole script for one day, then run it for the next day, and so on. I have Visual Studio, is there a way I can write some code that does the loop there, and just calls the script and passes it the two date variables that I need?

fredv

If I were to do this, I would write a stored procedure that take the start date and the end date as the input parameters and loop through the start date and the end date in increment of one day. Use your favorite search engine with the query "sql server datetime function". That will give me many examples of how to manipulate datetime inside the stored proc.

Sami

Here's a simple C# console app that takes in two parameters, database name and table name. It's assuming you're using SQLEXPRESS, if not then replace the connection name to whatever your server connection is. Using also integrated security (Windows login).

System.Environment.MachineName + "\\SQLEXPRESS" <- this is the same as "YOUR_COMP_NAME\SQLEXPRESS"

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            String ConnectionString = String.Format(
                "Data Source={0};Initial Catalog={1};Integrated Security=True",
                System.Environment.MachineName + "\\SQLEXPRESS",
                args[0]);

            SqlConnection con = new SqlConnection(ConnectionString);
            con.Open();
            if (con.State != ConnectionState.Open)
            {
                Console.WriteLine("failed to open database");
                return;
            }

            SqlDataAdapter da = new SqlDataAdapter("SELECT start, [end] FROM " +
                args[1], con);
		    DataSet ds = new DataSet();
            da.TableMappings.Add("Table", args[1]);
            da.Fill(ds);
            DataTable dt = ds.Tables[args[1]];

#if DEBUG
            DataRow nrow = dt.NewRow();
            nrow["start"] = DateTime.Now;
            nrow["end"] = DateTime.Now.AddHours(1);
            dt.Rows.Add(nrow);
            SqlCommandBuilder sc = new SqlCommandBuilder(da);
            da.Update(ds);
#endif

            foreach (DataRow row in dt.Rows)
            {
                PrintDates((DateTime)row["start"], (DateTime)row["end"]);
            }

            con.Close();
            
        }

        static void PrintDates(DateTime start, DateTime end)
        {
            Console.WriteLine("===========  Test Start ============");
            Console.WriteLine("Start Time:\t" + start);
            Console.WriteLine("End Time:\t" + end);
            Console.WriteLine("============  Test End =============");
        }
    }
}

AsSiMiLaTeD

lemme take a look at that, Sami

vlam

You don't need to loop. You want to use join (equivalent of a loop if done correctly). Yes, you can do this with .NET code but all you need is a stored procedure and call it with the 2 parameters.

I am working on a project at the moment using SQL 2005 so I may be able to help while I am playing with SQL

AsSiMiLaTeD

Okay, I've not yet worked with Stored procedures, so that'll be new to me. I could send you my script but it's big and nothing in there would make sense, so if you could give me an example I could work from that'd be great!