STORED PROCEDURES(SP):
A stored procedure is a precompiled set of Transact SQL statements that can execute a set of commands on your database server. Stored procedures can have parameters passed to them and also return result sets. The syntax for creating a stored procedure is
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} [VARYING] [= default] [OUTPUT]
]
AS
sql_statement [...n]
Arguments:
procedure_name :Is the name of the new stored procedure.
;number :Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement.
@parameter :Is a parameter in the procedure. A stored procedure can have a maximum of 1,024 parameters. Specify a parameter name using an at sign (@) as the first character.
AS :Specifies the actions the procedure is to take.
sql_statement :Is any number and type of Transact-SQL statements to be included in the
procedure.
Example 1:
Once of the first stored procedures I designed would take data from a table testDB.report_2 with three fields (int,int,varchar) and place it into a new table testDB.report_table with two fields (int,text)
DECLARE
@I INT,@J INT,@K INT,@ptrval VARBINARY(16),@com VARCHAR(255)
SELECT @1=650000 SELECT @J=1
WHILE @I<=650020 /* OUTER LOOP
BEGIN
INSERT INTO report_table VALUES(@I,'report')
SELECT @K=MAX(line_num) FROM report_1 WHERE report_num=@1
WHILE @J<=@K /*INNER LOOP
BEGIN
SELECT @ptrval=TEXTPTR(report) FROM report_table WHERE report_num=@I
SELECT @com=(SELECT report FROM report_1 WHERE line_num=@J and report_num=@I)
UPDATETEXT report_table.report @ptrval NULL 0 @com
END
SELECT @com='' SELECT @J=1 SELECT @K=1 SELECT @I=@I+1
END
Comments:
1. In order to assign local variable in SP, make sure you use @ before the variable. You can assign variables of any type except for image and text datatypes.
2. Using nested do loops is a little tricky compared to the popular nested for-loops which SQL Server does not allow STRANGE! After the loop terminates with the END statement, make sure you increment the counter otherwise you know what happens INFINITE LOOP! which we never want
3. In order to initialize a variable in SP, you have to use a SELECT as you can see in the above code
4. A comment on SELECT @com=(SELECT report FROM report_1 WHERE line_num=@J and report_num=I. This was an interesting line. In order to assign one comment from table report_1 to the variable @com, one has to use a SELECT statement. Also notice the conditions line_num=@J and report_num are necessary otherwise you will get an error "Subquery returned more than one value".
This stored procedure is used to convert a date field(VARCHAR) and a time field(VARCHAR) into a SQL datetime field. In this particular case we had to repeat the procedure for six different fields. Also we use a while-loop to repeat the commands for every records
CREATE PROCEDURE sp_datetime AS
DECLARE
@I INT, @MAX_ID INT
SELECT @I=1
SELECT @MAX_ID=3312
WHILE @I<=@MAX_ID
BEGIN
UPDATE stroke_data
SET onset_datetime=CONVERT(DATETIME,onset_date+''+onset_time)
WHERE unique_id=@I
UPDATE stroke_data
SET er_datetime=CONVERT(DATETIME,er_date+''+er_time)
WHERE unique_id=@I
UPDATE stroke_data
SET exam_datetime=CONVERT(DATETIME,exam_date+''+exam_time)
WHERE unique_id=@I
UPDATE stroke_data
SET first_datetime=CONVERT(DATETIME,first_date+''+first_time)
WHERE unique_id=@I
UPDATE stroke_data
SET consult_called_dt=CONVERT(DATETIME,consult_called_date+''+consult_called_time)
WHERE unique_id=@I
UPDATE stroke_data
SET consult_arrived_dt=CONVERT(DATETIME,consult_arrived_date+''+consult_arrived_time)
WHERE unique_id=@I
SELECT @I=@I+1
END
Remarks:1. The procedure executed without any major problems. There was a problem with 3 different rows which had a date that was incorrect. I used the UPDATE command to correct it and then ran the procedure
2. A bigger problem was the fact that there were a lot of NULLs in the final datetime fields. The reason for this is due to the fact that the data was not entered in the correct format for the time. Notice the following examples for the outcome:
Problems Date Time ---> Datetime
-------------------------------------------------------------------------
1. null null Jan 1 1900 12:00 AM
2. 09/12/97 1235 null
3. empty empty Jan 1 1900 12:00 AM
4. 09/12/97 Sept 09,1997 12:00 AM
-------------------------------------------------------------------------
Problem 2 is being solved by changing the time format to hh:mm:ss
Problem 4 is not a big issue because the date is correct and time information is ignored
Problem 1 and 3 still persist in the database table. Initially we used if..then statements along with the convert function to write NULLS instead of Jan 1 1900 12:00 AM. This was successful in cases where we enter nulls initially. If the user enters the correct format for date and time, the datetime columns would revert to Jan 1 1900 12:00 AM. This was very frustrating and we don't have a solution for this problems yet