SQL Newbie - stored procedure

• Michael Graves

  Old Hand

  Points: 305

  More actions

  May 28, 2005 at 6:38 am

  #163842

  I’m creating an SQL database from an existing MS Access mdb. I’ve created the tables in SQL and in the process of setting up an MS access ADP to act as the front end. But now I’ve hit a brick wall as my SQL is still in the early stages.

   

  The problem, there is a table contracts which holds contract information for lecturers which as fields for startdate, enddate and duration these contracts are for a specific day e.g. the contract is for any Tuesdays during the start & end date. I also have a table called dates, which holds all the dates during the academic year, and also which ones are workdays in term time. In the old mdb on the click of a button it ran some code that called a append query to up date the sessions table with all the workable dates between the start & end date of the contract.

   

  I think what I need to do is create Sproc to do this for me in SQL as there is no append queries in SQL.

   

  The old code was

  Private Sub InsertNewSessions()

  Const MSGBOX_TITLE = "Insert Sessions Error"

  Const MSGBOX_TYPE = 16

  If Not IsNull([TotalHrs]) Then

  MsgBox "This is a total hours contract.  You can't create sessions for it.", MSGBOX_TYPE, MSGBOX_TITLE

  elseIf IsNull([StartDate]) The

  MsgBox "You need to enter a start date.", MSGBOX_TYPE, MSGBOX_TITLE

      Else

          Set db = DBEngine.Workspaces(0).Databases(0)

          Set qd = db.QueryDefs("Insert_Sessions")

          qd("p_contract_id") = [ContractID]

          qd("p_start_date") = [StartDate]

          qd("p_end_date") = [EndDate]

          qd("p_hours") = [Duration]

          qd.Execute

      End If

  End Sub

   

  The sql code for th append query was

   

  PARAMETERS p_contract_id IEEESingle, p_start_date DateTime, p_end_date DateTime, p_hours IEEEDouble;

  INSERT INTO Sessions ( ContractID, [Date], Hrs )

  SELECT [p_contract_id] AS Expr1, Dates.Date, [p_hours] AS Expr2

  FROM Dates

  WHERE (((Dates.Date) Between [p_start_date] And [p_end_date]) AND ((Dates.Worked)=True) AND ((Weekday([Dates].[Date]))=Weekday([p_start_date])))

  ORDER BY Dates.Date;

   

  Hope that someone can help.

   

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  May 30, 2005 at 7:37 am

  #561693

  I'm not sure if I understand you, but your append query translated to  a stored procedure might look something like this:

  CREATE PROCEDURE dbo.InsertSomething @startdate DATETIME, @enddate DATETIME, @hours FLOAT

  AS

   INSERT INTO Sessions ( ContractID, [Date], Hrs )

    SELECT [p_contract_id], Dates.Date, @hours

     FROM Dates

     WHERE (((Dates.Date) Between @startdate And @enddate)

     AND ((Dates.Worked)=True)

     AND ((Weekday([Dates].[Date]))=Weekday(@startdate)))

    --ORDER BY Dates.Date;

   RETURN 0

  GO

   

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]