About   -   Contact   -   Purchase   -   Search   -   What's New

Stored Procedure Creation(1)
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.


Top of Page

Legal Notice

Ken Howe 2011