Please note that the information on this page is related to an older version of Microsoft SQL Server.

Click on SQL Server Tutorials for new training content and material. In addition we have FREE SQL Tutorial videos here, so feel free to download the demos. Our partners also manage two blogs on the following SQL Server Versions:

-SQL Server 2008 Blog
-SQL Azure Blog

Please send any comments or suggestion to info@sqlserver2008tutorial.com

STORED PROCEDURES(SP):

A stored procedure is a pre compiled 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)

This is what it looks like in SQL Server



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".

  Example 2:

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

This is what it looks like in SQL Server



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

Example 3:

Importing data from a text file into SQL Server:

The text file is stored in ascii format. It has three pieces of information. The first is the report number, the second is the line number and the third is the actual report. The problem we face is that for every report number, the data is stored in line numbers with the same report number. For example

Report_num     Line_num     Report
--------------------------------------------------------
650000     1         Samaritan hospital
650000     5         397-22-1234
650000     8         ------------------
---------     n         ------------------

We not only want to import this data into an SQL Server table but we want the format in such a way that for every report number, we just have one report and no line number.

STEP 1:

As the data is in ASCII format, we can use our favorite bcp command to import it into an SQL table

Souce location: F:\kashef\archive_table_pre.txt
Destination location: ACOM\testDB.archive_table_pre
The syntax of the command we used was
bcp testDB.dbo.archive_table_pre in F:\kashef\archive_table_pre.txt /Usa /S ACOM /f C:\bcp.fmt
The rows were copied successfully into archive_table_pre

STEP 2:

The second step involved writing a stored procedure that would involved two while-loops to process the data in the right format. The outer loop will loop through all the report numbers one by one and find the maximum number of line numbers. Once we get the max line number, then the inner loop starts. While the report number stays the same, the loop assigns the comment for every line to a pointer variable. This pointer variable is then assigned to a VARCHAR variable which is then appended to the text field in table report_table. If none of this is making sense to you, please look at the code.

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 @I=report_num
WHILE @J<=@K /*INNER LOOP
BEGIN
SELECT @ptrval=TEXTPTR(report)FROM report_tableWHERE report_num=@I
SELECT @com=(SELECT report FROM report_1 WHERE @J=line_num and@I=report_num)
UPDATETEXT report_table.report @ptrval NULL 0 @com
END
SELECT @com='' SELECT @J=1 SELECT @K=1 SELECT @I=@I+1
END