Scripting and creating stored procedures may appear an
elaborate and unnecessary task, especially when queries can easily be hard-coded
into applications, but the advantages of user-defined stored procedures,
usp's, clearly outweigh the disadvantages.
For a start, in enterprise wide architecture employing multiple
application servers, effecting amendments to a usp on the DB server is
quicker and easier to action than re-writing, re-compiling and
re-distributing applications containing hard-coded SQL. In addition,
queries run significantly faster as usp's due to the fact they're
pre-compiled and reside on the DB server.
Writing Usp's is essentially the same as hardcoding SQL in VB, except
for the use Trans-SQL and input/output parameters. Below is an example of a
script which creates usp returning an independent recordset.
CREATE procedure usp_retrieve_male_staff AS
SELECT firstname, lastname FROM lut_employees
WHERE employee_type = 'Male'
ORDER BY lastname, firstname
This returns a recordset of all male employees from the employees
table.
However, if requirements exist to filter a recordset based on specific
criteria, then input parameters are used. Below is a usp script with 2
filters passed in as parameters: CREATE procedure usp_retrieve_staff_filter_name_grade
@namewildcard varchar(8), @startdate datetime AS
SELECT firstname, lastname FROM lut_employees
WHERE (firstname LIKE '%' + @namewildcard + '%'
OR lastname LIKE '%' + @namewildcard + '%')
AND start_date >= @startdate
ORDER BY lastname, start_date
This usp is called from the application as followed: With rsDemo
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.ActiveConnection = Nothing
End With
With cmmDemo
.Prepared = True
.ActiveConnection = cnConnection
.CommandType = adCmdStoredProc
.CommandText = "usp_retrieve_staff_filter_name_grade"
.Parameters.Append .CreateParameter("@namewildcard ", _
adVarChar, adParamInput, 8)
.Parameters.Append .CreateParameter("@startdate", _
adDateTime, adParamInput)
.Parameters("@namewildcard ").Value = "Smith"
.Parameters("@startdate").Value = "01/01/2000"
rst.Open cmmDemo
End With
Next time we will look at return values as output parameters, instead
of recordsets.
|
