SQL Server Stored Procedure instance Solution

Task Description and requirements:

An existing database, SQL Server 2000, contains 72 hours of highway toll station data. Current requirement: query the number of vehicles passing through each toll station every ten minutes in the database within 72 hours. For example, if you query the toll station in North Jinan, the number of vehicles passing through the toll station in North Jinan is counted from 00:00:00 to 00:10:00. After the statistics, count the number of vehicles passing through the Jinan North toll station from 00:10:00 to 00:20:00, and so on. It is best to compile a program to show the number of vehicles at a toll station at intervals of 10 minutes.

The program code is as follows:

The following steps are used to test whether the SQL code is correct. The final Code takes only three or four steps. -- Step 1: number of Jinan North stations within one minute-test whether the statement is correct select * From trafficdata where dot1name = 'jinan north' and time1> = '2017-07-22 00:00:00 'and time1 <'2014-07-22 00:10:00 'or dot2name = 'jinan north' and time2> = '2014-07-22 00:00:00' and time2 <'2014-07-22 00:10:00'; -- Step 2: print the number of vehicles at an interval -- test whether the SQL Server Stored Procedure syntax is correct -- "@ temp1 = convert (varchar (3), @ temp) "indicates converting int type variables into varchar type variables create procedure getcountasdeclare @ temp1 varchar (3) Declare @ temp intselect @ temp = count (*) from trafficdata where dot1name = 'jinan bei' and time1> = '2017-07-22 00:00:00 'and time1 <'2017-07-22 01:00:00' or dot2name = 'jinan bei' and time2> = '2014-07-22 00:00:00 'and time2 <'2014-07-22 01:00:00 '; set @ temp1 = convert (varchar (3), @ temp) print @ temp1 -- Step 3: Create a stored procedure -- this stored procedure has no input parameters, print the required data directly -- "dateadd (MI, 10, @ start_date) "indicates that the function adds the variable @ start_date to 10 minutes -- while loop part begin -- end pairing drop procedure mysp_para create procedure mysp_para as declare @ start_date datetime -- loop start time declare @ end_date datetime -- loop End Time declare @ temp2 varchar (3) -- number of vehicles used to receive each cycle varchar type declare @ temp1 varchar (8000) -- the number of vehicles in all cycles is separated by commas (,). Declare @ temp int -- number of vehicles in each cycle. Int type: declare @ size int -- number of cycles declare @ address varchar (10) -- toll station name set @ start_date = '07 23 2005 '; Set @ size = 0; Set @ address = 'jinan north'; Set @ temp2 = ''; set @ temp1 = ''; while (@ size <3*24*6) Begin set @ end_date = dateadd (MI, 10, @ start_date); Set @ temp = 0; select @ temp = count (*) from trafficdata where dot1name = @ address and time1 >=@ start_date and time1 <@ end_date or dot2name = @ address and time2 >=@ start_date and time2 <@ end_date; set @ temp2 = convert (varchar (3), @ temp); Set @ temp1 = @ temp1 + @ temp2 + ','; Set @ start_date = @ end_date; set @ size = @ size + 1; endprint @ end_dateprint @ temp2print @ temp1print @ start_dateprint @ size go -- Step 4: Run -- run the stored procedure, obtain the data of all vehicles passing through the north of Jinan within three days starting from, in the format ,..... "exec mysp_para