SQL Newbie - stored procedure

Question

SQL Newbie - stored procedure

Michael Graves

SSC Veteran

Points: 275
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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]