SQL_StoredProcBasics Updated 10/05/2001

BUSINESS & COMPUTERS, Inc.
13839 Mur-Len Rd, Suite M OLATHE, KANSAS 66062
We Translate Business Processes from the Mind to the Computer to the Bottom Line.

Phone: (913) 764-2311 Fax: 764 7515 larryg@kcnet.com

Stored Procedures for SQL Server The Basics
Copyright® 2001 Business & Computers, Inc. A note – the below is my humble opinion – with testing – If you use my ideas please test them and if you have problems or learn more let me know.

#1 - Stored Procedures (SPs) Inside SQL Server * Stored Procedures are precompiled Transact-SQL statements stored in a SQL Server database. * Stored Procedures are one of the most powerful pieces of programming you will ever see. When you start out, you will see them as a way to return a record set, or do some small update on your data. As you learn more about SPs you will understand why there are entire books written on the subject. SQL Server compiles the Proc so that when you run it, it runs as fast as possible. Once you write a couple of complicated SPs, you will be convinced. This paper only covers the tip of the Stored Procedure iceberg. * I will refer to Stored Procedures in this document as SP and Proc - get use to it. * Stored Procedures return read only data and can have > Input parameters > Output parameters > Parameters that are both input and output > Can have 1 or more recordsets

Business & Computers, Inc.

Page 1

#2 - Simple Recordset with a Input Parameter * Figure –2-1 shows a simple stored procedures with that has in input parameter and returns a recordset. When we run it from the Query Analyzer (Figure 2–2) we get the following results.

Figure 2-1 Stored Procedure with input parameter & recordset

Stored Procedure Name

Parameter Check Syntax

Run Button

Database running against

Result set

Figure -2-2 Running a procedure on Query Analyzer

* If you notice in Figure 2-2, it shows “(3 row(s) affected)”. If you don’t set “set nocount on” in a SP, when you run the SP in the Query Analyzer, you will get back a message “X rows affected”. By setting nocount on, it stops SQL Server from doing some work, that you don’t care about. This will cause the SP to run just a little faster. * You need to learn about sp_Help and other system stored procedures. Works with or without the single quotes.

Figure 2-3

* You can also run the query in an Access Pass-Through Query. Figure 2-4 MS Access Pass-Through Query Figure 2-5 Pass-Through Query Results

Business & Computers, Inc.

Page 2

* In figure 2-4 we use ADO code and the command object to get a recordset from the Stored Procedure on SQL Server. * Note: You certainly can do this many different ways, however I do want to point out the difference between the While, Wend Loop as opposed to the GetString. You will probably want to use the GetString in testing.

Figure 2-4 ADO using Proc for recordset
Public Function ex_SP_ReadRecords() '--> Uses the Command Object Dim Cmd1 As ADODB.Command Dim lngRecordsAffected As Long Dim rs1 As ADODB.Recordset Dim intRecordCount As Integer '----Dim cnnTemp As ADODB.Connection Set cnnTemp = New ADODB.Connection cnnTemp.ConnectionString = "Provider=SQLOLEDB.1;" & _ "DRIVER=SQL Server;SERVER=bcnt;" & _ "Trusted_Connection=Yes;UID=;PWD=;" & _ "DATABASE=MWData;" cnnTemp.Open 'Open Connection Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = cnnTemp '--With Cmd1 .CommandText = "z_sp_SimpleReadTable" .CommandType = adCmdStoredProc .Parameters.Refresh .Parameters("@vcCompanyName").Value = "bus" End With

Set rs1 = Cmd1.Execute() 'While Not rs1.EOF ' intRecordCount = intRecordCount + 1 ' Debug.Print rs1.Fields(1), intRecordCount ' rs1.MoveNext 'Wend 'The following lines shows all the records and all fields fro the above recordset Debug.Print rs1.GetString(adClipString, , ";") rs1.Close Finish_Up: ex_SP_ReadRecords = True ProcedureDone: On Error Resume Next rs1.Close Set Cmd1 = Nothing Set rs1 = Nothing Exit Function HandleError: Debug.Print Err.Number, Err.Description Resume ProcedureDone End Function

Business & Computers, Inc.

Page 3

#3 - Simple Input & Output Parameters * Figure 3-1 shows another example of a simple SP with input and output parameter. In the SP we input a company Id (@vcCo_IdT) and return the company name in the output parameter. We run the SP with ADO Code. (see figure-6)

Figure 3-1 Stored Procedure Input and Output Parameters

The Proc simply takes the input from the ADO code, runs the T-SQL statement using the input parameter, and returns the answer to the ADO code.

Figure -3-2

Comment

/*Input @vcCo_Idt =266 OutPut @vcOutPut1 = Business & Computers, Inc */

* Notice the line in Figure-3-2. This is a remark. You can put in Figure -3-3 ADO Code to run Proc a remark with “/*” and end with Public Function ex_SP_In_Out_Parameters_Simple_2() “*/” You can also use two dashes “--this is a test” for a single line. 'On Error GoTo HandleError * The ADO code (figure-3-3) Opens the connection, sets the command, refreshes the parameters, and set the value of the parameter, and then executes the proc. It then reads the output parameter from the proc.
Dim Cmd1 As ADODB.Command Dim lngRecordsAffected As Long Dim cnnTemp As ADODB.Connection Set cnnTemp = New ADODB.Connection cnnTemp.ConnectionString = "Provider=SQLOLEDB.1;" & _ "DRIVER=SQL Server;SERVER=bcnt;" & _ "Trusted_Connection=Yes;UID=;PWD=;" & _ "DATABASE=MWData;" cnnTemp.Open '----

Note: If you run this procedure 'Open Command Object from the query analyzer, you will Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = cnnTemp need to put in a false parameter for the output parameter, and probably put a print statement in- '--With Cmd1 side the proc to show the output .CommandText = "z_sp_In_Out_Parameters_Simple" parameter in the query analyzer. .CommandType = adCmdStoredProc
.Parameters.Refresh .Parameters("@vcCo_IdT").Value = 266 .Execute , lngRecordsAffected, adExecuteNoRecords End With Debug.Print Cmd1.Parameters("@vcOutPut1").Value

z_sp_In_Out_Parameters_Simple '266', ''

print @vcOutPut1

Set Cmd1 = Nothing ProcedureDone: Exit Function HandleError:
Business & Computers, Inc.
Page 4

CREATE PROCEDURE z_sp_In_Out_Parameters_Complex Figure 3-4 Com@vcWhere AS varchar(8000), plex Input and Output @vcTableFromName AS varchar(255) , Parameters @vcIDName AS varchar(55) , @vcTableInToName AS varchar(255) = 'tbl_zs_StartID', @btNumericId_YN as bit=0, @vcOutPut1 AS varchar(255) output as SET NOCOUNT ON

* This is a bit of a /* Documentation Below '---complex stored --> Purpose: This is an Example of In & Out Parameters procedure. I See ex_SP_In_Out_Parameters_Complex in Mod_ADo_SQL Required_Elements: tbl_zs_StartID Returns: Recordset see a direction -----------------------------------------------we are heading. Documentation Above */
Declare @vcSQL AS varchar(255) Declare @vcIDField AS varchar(55) set @vcOutPut1 = '-100' Select @vcSQL = 'delete from ' + @vcTableInToName exec(@vcSQL) set @vcOutPut1 = '-90' --What field do we put the data into If @btNumericId_YN =0 Begin Select @vcIDField = 'IdT' End else Begin Select @vcIDField = 'Id' End set @vcOutPut1 = '-80' --Put the Id from the records in the current form into the table Select @vcSQL = 'INSERT INTO ' + @vcTableInToName + '( ' + @vcIDField + ')' + ' SELECT ' + @vcIDName + ' FROM ' + @vcTableFromName + ' Where ' exec(@vcSQL + @vcWhere) set @vcOutPut1 = '-70' --SELECT @chmsg = 'We are Done.' --select @vcOutPut1 = str(@@rowcount) SELECT vw_Companies.Co_Alpha_Name, vw_Companies.Bill_Cty, vw_Companies.Bill_St FROM vw_Companies RIGHT OUTER JOIN tbl_zs_StartID ON vw_Companies.Co_IdT = tbl_zs_StartID.IdT select @vcOutPut1 = '--->' + Ltrim(str(@@rowcount) + ' Records') return 10

Business & Computers, Inc.

Page 5

#4 - What Access Calls Action Queries (Delete data, Append Data, Update Data, Make Tables) * In MS Access we have select queries that would return a result set similar to figure 2-5. In addition we have the following type of queries that manip ulates the data in the tables. Delete data * Ok, so I made it a little more difficult than it had to be. To delete records from a table you can just have one line in the procedure: delete tbl_City where City_Id = @intId

Figure 4-1

Delete Records—Stored Procedure

Figure 4-2 ADO to Run the Above Delete Action

Public Function ex_SP_QueryDelete() As Boolean

You can pass an input parameter Create Procedure abc @intId as Int as

'>>> Stored Procedure & ADO are about the same Speed We delete table tmpCity if it exist.

Section A

Section B

Business & Computers, Inc.

Page 8

#5 - Case Statement * If you are like me and use the “IIf” statement in Access queries, you are going to want to know what you can replace it with in SQL Server. There are no replacements in Views, however in SPS you can use the case statement. In figure 5-1 we have a SP that looks at the field Mail_St which is a 2 character field for the state. If it = KS we substitute Kansas, if MO we use Missouri, otherwise we use the actual value in the field Mail_St. You can see how it comes out in figure 5-2.

Figure 5-1

Figure 5-2

Business & Computers, Inc.

Page 9

#6 - Additional Information

Figure 6-1 SQL Floor(7.234) Round(765.4321, 2)

>>Numeric Functions>Type Conversions>Misc. InformationBetween ‘1/1/01’ and ‘12/31/01’

String Delimiter Concatenation Operator

SQL -> ‘Gordon’ + ‘, ‘ + ‘Larry’ SQL -> ‘Gordon’ + ‘, ‘ + ‘Larry’

‘ + _ % 1 0

Wildcard Character (Any one character) Where Last like “Gor_on” SQL -> select last_Name from tbl_Individuals where last_name like 'Gor_on' Wildcard Character (Any group of characters) SQL -> select last_Name from tbl_Individuals where last_name like 'Gord%' True/Yes Bit type data False/No Bit type data

Business & Computers, Inc.

Page 10

Figure 6-4 SQL

>>String Functions