http://www.developer.com/

Back to article

Quick Start Guide to SQL Server 7 -- Part 4


November 19, 2002

Welcome to the fourth and final part of our quick start SQL Server 7 guide!

Turn on that printer and get ready to roll out another dozen pages of simple step-by-step instruction, guaranteed to get your database solution up-and-running within minutes.

If you've missed our previous three instalments, check out part one here, part two here, and part three here.

Congratulations on coming this far and I wish you all the... oh, let's just forget all that soppy stuff and dive straight into the content!

Transactions allow you to complete numerous database operations as one whole unit.

Let's take the common example of bank transactions. Mr Bloggs walks into his local Natwest and hands over a cheque given to him by Mr Smith.

After this is tapped into the computer, your application may remove the money from Mr Smith's account... and then crash. That means Mr Smith has lost his money and Mr Bloggs is left wondering what's happened to his cheque payment.

Or perhaps your system first adds the money to Mr Smith's account, then attempts to deduct it from the account of Mr Bloggs. What happens if Mr Bloggs doesn't have adequate cash reserves? That means Mr Smith now has money in his account that shouldn't really be there.

All these problems are solvable with transactions. You can 'start a transaction', then take the money from Mr Smith, give it to Mr Bloggs, then 'commit the transaction'.

Committing the transaction tells SQL Server everything is fine.

However you can also 'rollback the transaction'. After deducting money from Mr Smith and adding it to the bank account of Mr Bloggs, you might experience an error. Maybe Mr Bloggs has since closed down his account, or something similar.

You can then 'rollback' (cancel) the transaction, meaning Mr Smith doesn't have any money deducted, nor Mr Bloggs have any money added.

In other words, both items of work are either completed or rejected as one entire unit not individual database operations.

Let's now look at a simple Visual Basic transaction. This code attempts to add two extra records to the Jobs table. It then prompts the user if they want to commit or rollback.

If they commit, both records should be added. If they abort, neither should be added.

In real life however, these records would probably be in different tables. And transactions aren't limited to record adding. You can commit and rollback anything from stored procedures to table creation.

And don't forget that you wouldn't usually prompt a user to commit or rollback. You would typically commit or rollback in code depending on whether you received errors (eg, as a result of data integrity, etc).

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objRS As New ADODB.Recordset' Objects to be used in this operation -' objConn, the basic connection' objRS, to hold our recordsetWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseobjConn.BeginTrans' Begin Transactions here!objRS.Open "Select * from Jobs", objConn' Open the recordset, selecting all from the Jobs tableobjRS.AddNewobjRS.Fields("job_desc") = "My New Test Job"objRS.Fields("min_lvl") = 10objRS.Fields("max_lvl") = 20objRS.Update' Add a new record... and then...objRS.AddNewobjRS.Fields("job_desc") = "My Second New Test Job"objRS.Fields("min_lvl") = 20objRS.Fields("max_lvl") = 30objRS.Update' ... another!If MsgBox("Do you want to commit?", vbYesNo + vbQuestion) = vbYes ThenobjConn.CommitTransElseobjConn.RollbackTransEnd If' Run the CommitTrans or RollbackTrans methods,' depending on user responseobjRS.CloseSet objRS = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Triggers give the developer more control over their data.

Not comparable with anything in Microsoft Access, triggers allow the developer to really control what information is allowed into the database, as well as what happens with it.

Triggers help maintain data integrity.

They fire off after certain events, such as the addition, update or deletion of a record.

Let's look at a few possible trigger uses:

  • Enforce business rules if you have a few complex business rules that you can't handle via regular relationships and table constraints, you can throw them into triggers. For example, you might want to check every order to ensure the customer hasn't ordered more than three special offer video recorders
  • Log transactions Perhaps you want to keep a log of what is happening within the database. Every time a record is altered in any way, you can write code to throw a log entry into a separate table (perhaps even a separate database)
  • Maintain data integrity Relationships are really only triggers under another name. Every time you add or change records, a hidden trigger fires off and checks for data integrity. You can add your own using triggers, or perhaps implement Access features not supported by SQL Server relationships, such as Cascade Update and Cascade Delete

We'll attempt to cover the basics of implementing trigger statements in this section, though it's a complex topic and can be difficult to grasp. Don't worry if you don't understand it all at first.

To manage Triggers:

  • View your Tables (as before)
  • Right-click your Table
  • Select 'All Tasks', 'Manage Triggers'

Let's now take a peek at a few sample triggers.

This trigger checks if a certain field in Table1 contains a particular value. If it does, that record is rejected:

CREATE TRIGGER MyTriggerName ON [TABLE1]-- This says create a trigger called MyTriggerName to monitor Table1 --FOR INSERT, UPDATE-- And launch this trigger every time an insert or update ---- is made on this table. The three default options are  ---- INSERT, UPDATE, DELETE - add or remove as appropriate --ASDeclare @MyVariable as Char(10)-- This declares a variable called MyVariable as Char(10), ---- a string data type to hold a default ten characters --Select @ MyVariable = Inserted.MyFieldName from Inserted-- When inserting/update information, SQL Server creates a temporary ---- table (Inserted) to hold the information. When deleting, it uses ---- a table called Deleted. This bit of code uses an SQL statement ---- to retrieve information from the Inserted table and place ---- it into the MyVariable variable --If @MyVariable = "Karl"-- If MyVariable equals Karl then --	Begin	   Raiserror('Invalid entry. Choose another name', 16, 1)	   -- Raise an error --	   Rollback Transaction	   -- Discard the record -- 	End	-- Note that the Begin and End statements are just the --	-- boundaries of what should happen after the If statement --

Here's another example trigger:

CREATE TRIGGER SalaryLog ON [MyEmployees]FOR UPDATE-- Launch on an update --ASDECLARE @EmpName as VarChar(100), @EmpSalary as Money-- Declare two different variables of different data types --SELECT @EmpName = Inserted.EmployeeName,@EmpSalary = Inserted.SalaryFROM Inserted-- Grab the stuff being updated and place it into the variables -- INSERT INTO MyLog(Username, TheDate, Alteration)Values (USER_NAME() , GETDATE(), 	@EmpName + ' is now on ' + CONVERT(VarChar(10), @EmpSalary))-- Insert the logged on database user and the date into the log table ---- Also, insert a description - @EmpName added to ' is now on ' added ---- to @EmpSalary, which has been converted from the Money data type ---- to a varchar using the CONVERT function --

It's worth noting that triggers only fire off once even if you're altering multiple records. For example, you may be performing a mass update or delete and if just one of those records violates the rules of your trigger and you issue a ROLLBACK TRANSACTION, it'll stop the whole lot.

Also, triggers can't be called manually from within Visual Basic. They're simply 'triggered' by SQL Server.

For more information on triggers, look up the topic in the Books Online reference that ships with SQL Server 7.

Stored Procedures (SPs) are chunks of code that reside on the server. Each SP can perform a wide variety of tasks.

You can call a stored procedure from within Visual Basic, perhaps passing a required argument or accepting a 'return value'.

Let's look at a few of the advantages using stored procedures gives us:

  • Easy Maintainability Stored procedures can get very complex, allowing you to moving difficult code from the client program and place it on the server. This allows for easier maintenance
  • Network Traffic Asking the server to do a chunk of work with a stored procedure, as opposed to the client executing individual server statements can really reduce network traffic
  • Greater Security - Some companies only allow developers permission to execute and retrieve database information via stored procedures. This means less chance of mishaps, as direct access to the underlying tables is never granted

Over the next few pages, we're going to cover four common stored procedures, alongside calling Visual Basic code (without error handling for simplicity). In addition, we'll also be demonstrating output parameters.

All of this code can be used with the sample Pubs database that ships with SQL Server. Don't forget to ensure the user ID you use has execution permissions for the stored procedure. Also, remember to add a project reference to the ADO Library before running the Visual Basic code.

To create a Stored Procedure:

  • View your Database in Enterprise Manager (as before)
  • Right-click on the 'Stored Procedures' icon, selecting 'New Stored Procedure'
  • Type your stored procedure statement
  • Click OK to Save

To edit a Stored Procedure:

  • View your Database in Enterprise Manager (as before)
  • Click the 'Stored Procedures' icon
  • Double-click on your stored procedure
  • Edit as required
    • You can't change the name of a stored procedure once you've created it. To do this, you'll have to completely recreate it, then delete the original
  • Click OK to Save

Let's look at an example stored procedure that accepts a parameter and returns a set of records to the client.

CREATE PROCEDURE SelectTitleByKeyword _       @keyword varchar(50)ASSELECT Title, Title_ID, TypeFROM TitlesWHERE Title LIKE '%' + @keyword + '%'

This stored procedure is called SelectTitleByKeyword. It accepts a keyword of type varchar(50), a string up to 50 characters long.

It then returns the results of our SQL statement, which searches for any items with the passed keyword in the title.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim objRS As RecordsetDim strBookTitle As String' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' objRS, to hold the returning recordset' strBookTitle, a string to hold the book keywordWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseWith objCommand.CommandType = adCmdStoredProc.CommandText = "SelectTitleByKeyword".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withstrBookTitle = InputBox("Enter a keyword from the book you are looking for:", _"Book Keyword", "computer")' Put the keyword into the array. We need to use arrays' for this, even if we're just working with one argument.Set objRS = objCommand.Execute(, strBookTitle)' Execute the stored procedure, passing it the parameterIf objRS.BOF = False And objRS.EOF = False ThenCall MsgBox("The first book I found containing your " & _"keyword was:" & vbNewLine & objRS.Fields("Title") & _vbNewLine & "The book ID number is " & _objRS.Fields("Title_ID") & vbNewLine & _"This book is classified under " & _objRS.Fields("Type"), vbInformation)ElseMsgBox ("No books contained the keyword: " & strBookTitle)End If' Hurrah! We now have a recordset to do what we want with!Set objRS = NothingSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Let's look at an example stored procedure that accepts two parameters and updates records in a table.

CREATE PROCEDURE UpdateTitle @OldTitle varchar(80), _       @NewTitle varchar(80)ASUPDATE TitlesSET Titles.Title = @NewTitleWHERE Titles.Title = @OldTitle

This stored procedure is called UpdateTitle. It accepts two arguments, an OldTitle of type varchar(80) and a NewTitle also of type varchar(80).

In this SQL statement, both the OldTitle and NewTitle arguments refer to the Title field in the Titles table. This is of type varchar(80), hence why the parameters are also of the type varchar(80). You can view the field data types by viewing the properties of a table (as before).

This stored procedure doesn't return anything. It just updates the base table.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim Params(1 To 2) As Variant' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' Params(1 To 2), a variant array to hold the two'  arguments this stored procedures requires'   NOTE: When passing arrays across to stored procedures'   you must declare them as the variant type, unless'   you like the phrase 'major problems'...With objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseWith objCommand.CommandType = adCmdStoredProc.CommandText = "UpdateTitle".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withParams(1) = InputBox("Enter the book title to change:", "Book Title", "Net Etiquette")Params(2) = InputBox("Enter the new title for " & strParams(1), "New Title", "My New Title")' Put the old title and new title into the array,' ready to pass as arguments to the stored procedureobjCommand.Execute , Params' Execute the stored procedure, passing it the parameterSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Let's look at an example stored procedure that accepts a numeric ID and deletes all records from the Employee table with the passed Job ID.

CREATE PROCEDURE DeleteJobType @JobID smallintASDELETE FROM EmployeesWHERE Employees.Job_ID = @JobID

This stored procedure is called DeleteJobType. It accepts an ID number of type smallint. It doesn't return anything.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim intJobID As Integer' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' intJobID, an integer to hold the Job IDs to be deletedWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseWith objCommand.CommandType = adCmdStoredProc.CommandText = "DeleteJobType".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withintJobID = InputBox("Which job type do you wish to delete?", "Enter Job ID", 14)' Grab the Job ID from the userobjCommand.Execute , intJobID' Execute the stored procedure, passing it the parameterSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Let's look at an example stored procedure that accepts a number of parameters, then uses them to insert a row into a table.

CREATE PROCEDURE CreateNewJobType@job_desc varchar(50), @min_lvl tinyint, @max_lvl tinyintASINSERT INTO jobs (job_desc, min_lvl, max_lvl)VALUES (@job_desc, @min_lvl, @max_lvl)

This stored procedure is called CreateNewJob. It accepts three parameters of various types. It then proceeds to insert a new record into the Jobs table using these arguments.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim Params(1 To 3) As Variant' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' Params(1 To 3), an array to hold the argumentsWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseWith objCommand.CommandType = adCmdStoredProc.CommandText = "CreateNewJobType".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withParams(1) = InputBox("Enter the job description (varchar(50)):")Params(2) = InputBox("Enter the minimum level (tinyint):")Params(3) = InputBox("Enter the maximum level (tinyint):")' Grab the arguments from the userobjCommand.Execute , Params' Execute the stored procedure, passing it the parameterSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Passing information back to your Visual Basic program via stored procedures is commonly done via recordsets. But that isn't always the best solution.

Sometimes you may want to pass your procedure an author ID, and have it return just the author's first and last names. Or perhaps you want a procedure to tell you exactly how many customers you currently have. Maybe you simply want to return a custom error message.

You can do all this via output parameters.

Let's look at an example stored procedure:

CREATE PROCEDURE Hello @HelloMsg varchar(50) outputASSET @HelloMsg="Good Morning from SQL Server!"RETURN

This procedure is called Hello and accepts a typical varchar parameter called HelloMsg. However note that it's followed by the 'output' keyword.

The procedure then sets the HelloMsg parameter to a simple message, and finishes off cleanly with the RETURN statement (not required but certainly neat, akin to an Exit Function command).

Let's now look at some Visual Basic code that could handle this. On the next page, we'll look at an example both more complicated and useful.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim objParam As Parameter' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' objParam, the parameter we're passingWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseWith objCommand.CommandType = adCmdStoredProc.CommandText = "Hello".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withSet objParam = objCommand.CreateParameter("TheHelloMessage", _adVarChar, adParamOutput, 50)' Create the parameter, stating its data type' and that it is an output parameter (adParamOutput).' The name of the parameter doesn't matter, but if' you're passing more than one, do so in the order' they're listed in the stored procedure.objCommand.Parameters.Append objParam' Add our parameter to objCommand's Parameters collectionobjCommand.Execute' Execute the stored procedure' Our parameter will be automatically passedMsgBox objParam.Value' After the stored procedure is altered, any return' value will now be inside objParam.ValueSet objParam = NothingSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Let's now look at another sample to demonstrate output parameters.

Consider this example stored procedure:

CREATE PROCEDURE GetAuthorNames@AuthorID id, @FirstName varchar(20) output, _              @LastName varchar(40) outputASSELECT @FirstName = au_fname, @LastName = au_lnameFROM AuthorsWHERE au_id = @AuthorIDRETURN

This procedure is called GetAuthorNames and accepts three parameters; an Author ID of type id (varchar), plus two output parameters, FirstName and LastName.

The procedure dips into the Authors database and sets the FirstName and LastName output parameters to the relevant au_fname and au_lname fields in the table.

Let's look at the Visual Basic code that could handle this.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim objParam1 As ParameterDim objParam2 As ParameterDim objParam3 As ParameterDim strAuthorID As String' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' objParam1/2/3, the parameters we're passing' strAuthorID, variable to hold requested author IDWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseWith objCommand.CommandType = adCmdStoredProc.CommandText = "GetAuthorNames".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withstrAuthorID = InputBox("Enter the author ID:", "Required", "341-22-1782")' Ask the user for the author IDSet objParam1 = objCommand.CreateParameter("AuthorID", _adVarChar, adParamInput, 11, strAuthorID)Set objParam2 = objCommand.CreateParameter("FirstName", _adVarChar, adParamOutput, 20)Set objParam3 = objCommand.CreateParameter("LastName", _adVarChar, adParamOutput, 40)' Create the parameters - both input and outputs types' Note that objParam1 is an input parameter and also' specifies a passed value, our strAuthorIDobjCommand.Parameters.Append objParam1objCommand.Parameters.Append objParam2objCommand.Parameters.Append objParam3' Add our parameter to objCommand's Parameters collection' You would typically do this straight after you create' the parameters. Also, you need to append these in the ' order the parameters are listed in the stored procedureobjCommand.Execute' Execute the stored procedure' Our parameters will be automatically passedIf IsNull(objParam2.Value) And IsNull(objParam2.Value) ThenMsgBox "The return parameters are null. So you " & _"probably entered an invalid author ID number!", _vbExclamation, "No Such Author ID: " & strAuthorIDElseMsgBox "Author ID: " & strAuthorID & vbNewLine & _"First Name: " & objParam2.Value & vbNewLine & _"Last Name: " & objParam3.Value, vbInformationEnd If' Check the parameters and display the values appropriatelySet objParam = NothingSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

That's the end of our quick start guide to SQL Server 7. I hope you've enjoyed the series.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date