A to Z of Stored Procedures for ASP and VB Programmers - Benny Alexander
The article is meant for ASP and VB Programmers, who always try to write hard core SQL statements embedded in their coding to communicate with database. If you read this article, Its time for you to learn and switch over into Stored Procedures. You can find a number of resources on stored procedures, But unlike others in this tutorial I tried to give you the maximum possible information , from the Theory to the debugging of stored procedures. I can always update this if you give your valuable suggestions. Write me to
When I was starting my carrier as a VB programmer, All I knew for connecting a Database with my front-end are, the Data control and RDO. Later When I moved to ASP, I was enjoying writing SQL statements. It was fun when the SQL statements were simple and hard when it comes to complex and nested queries. Writing direct SQL commands inside my VB or ASP program were not been a big issue to me, until I started thinking about performance of my applications. But when time came, I started learning Stored Procedures I was really encouraged to do possible re-factoring of my previous coding with Stored Procedures, and my life with direct SQL commands came to an end. I hope my SP(Stored Procedure) history was not boring. With an assumption that you are good in ASP or VB and SQL let us begin SPs.
SPs are an advanced feature in SQL server that offers you to create, compile and run SQL statements in the server itself, to isolate your business logic from data logic and to improve the performance of your application.
In short, write SQL queries in a specific format in the SQL server and call them from your application, instead of writing queries inside your program code.
1.Mainly to increase the performance and the momentum to our programs. When you write a stored procedure, it will be pre-compiled by the SQL server, so that it can increase the speed of executing the queries and hence your application. When you write a stored procedure the database(DB) server automatically generates an execution plan for the procedure. The plan will be updated whenever a change is made in the procedure by the DB server.
2.You can take away all your SQL commands so that the data logic can be isolated from your business logic(ie.,Coding). This kind of encapsulation helps the web server to read and interpret lengthy and complex SQL commands.
3.Then like COM, stored procedures can be reused. For example if you want to do the same query in two different ASP pages or in VB forms, you can reuse the stored procedure which you have written for one page, It saves you time.
4.The application code as well as the stored procedure code are becoming easy to maintain. Updating a stored procedure may not affect the other part of an application or user who uses the same stored procedure.
5.The queries can be customized by using input/output parameters, like functions and procedures which you write in your programming languages.
There are three types of stored procedures. Microsoft supplies several stored procedures to manipulate and administrate the database. Apart from them can write custom stored procedure to use them in our application level.
1. System stored procedures:
System stored procedures are mainly used for administrating, assisting, configuring and monitoring the SQL server.
Extended stored procedures are used to access SQL server by using dlls. We can use C or C++ to write extended stored procedures with Dlls. One good example is accessing the operating system commands on SQL server. We can use a stored procedure called xp_cmdshell to to run a DOS command, like the following one.
xp_cmdshell "dir c:\",will list the files in the root directory of C drive.
3. Custom stored procedures:
These are the stored procedures we write. There are several advantages in writing our own stored procedures. We are able to write complex and nested statements with less effort.
In this article we focus only on custom stored procedures, in the following section.
1. Using SQL Server Enterprise manager - To create a stored procedure, Open SQL server enterprise manager,
- Expand a server group; click and expand a server.
- Expand Databases; select the database where under which you want to create the procedure.
- Right-click Stored Procedures; then click New Stored Procedure.
- Type the stored procedure. Press CTRL-TAB to indent the text of a stored procedure.
- To check the syntax, click Check Syntax.
- To set the permissions, click Permissions.
2. Using Query Analyzer - Open query analyzer, select the DB, type the procedure and execute it.
3. From ASP Code - Write the procedures, create the connection with the DB using ADO, call the procedures directly from your code.
4. Using Visual Studio.NET - VS.NET users can write their procedures using "create New Procedure" like in Enterprise Manager and check the syntax there itself.
5.Create using Enterprise manager wizard - We can also use the wizard to create the procedures. Follow the steps to use the wizard.
- Expand a server group; then expand the server in which to create the view.
- On the Tools menu click Wizards...
- Expand Database.
- Double-click Create Stored Procedure Wizard.
- Complete the steps in the wizard.
Now let us see an example procedure and peek into it a while for the basic understanding and structure of any stored procedure. I prefer enterprise manager to write stored procedure, and during the time of learning I recommend you to use Enterprise manager.
BEGIN
SELECT au_fname,au_lname,title,pub_name,pub_year
FROM tblAuthors
WHERE pub_name = 'WROX'
END
GO
After tying the procedure you can immediately check the syntax. Here all the SQL commands will be written in the BEGIN...END block. Here sp_selauthor is the procedures name.
We have seen a simple stored procedure that just selects the required values from a table. Now let us see a little more complex stored procedure with parameters. Stored procedures allow input and output(i/o) parameters to have a better control over the i/o values.
Here is an example with an input parameter,@publisher_name varchar(50)
AS
BEGIN
SELECT au_fname,au_lname,title,pub_name,pub_year
FROM tblAuthors
WHERE pub_name = @publisher_name
END
GO
....................[EXAMPLE1]
In the above example, @publisher_name is the input parameter and the values will be sent by your ASP or VB code. We will see, how to send values shortly.
Another example , an Insert statement, with two input parameter can help us to understand better.
CREATE PROCEDURE sp_InsName
@FirstName varchar(20),
@LastName varchar(30)
AS
BEGIN
INSERT INTO Names(FirstName, LastName)values(@FirstName, @LastName)
END
GO
....................[EXAMPLE2]
Here is an example with an output parameter,
CREATE PROCEDURE sp_sel_no_authors
@count_authors int OUTPUT
AS
BEGIN
SELECT @count_authors = Count(*) FROM
tblAuthors
WHERE tblAuthors.pub_Name = "Wrox"
END
GO
....................[EXAMPLE3]
Here,the total no of authors who write for WROX press will be counted and sent through the output parameter. You can see the keyword OUTPUT to differentiate the output parameter from the input parameter.
FName = "Benny"
LName = "Alexander"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=macdb;uid=test;pwd=test"
sqlInsName = "sp_InsName '" & FName & "', '" & LName & "'"
objConn.Execute(sqlInsName) %>
In the following ASP code we are going to see how to use command object to execute a stored procedure and also to get a OUTPUT value from the stored procedure.
Here I am using the stored procedure EXAMPLE3 for illustration.
Dim objConn, objCmd, objParam
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=macdb;uid=test;pwd=test"
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "sp_sel_no_authors"
objCmd.CommandType = adCmdStoredProc
objcmd.ActiveConnection = objConn
Set objParam = objCmd.CreateParameter ("@count_authors",adInteger,adParamOutput,4)
objCmd.Parameters.Append objParam
ObjCmd.Execute
<HTML>
<BODY>
No of Authors write for wrox are: <%= objCmd.Parameters.("@count_authors")%>
</BODY>
</HTML>
You may need a little explanation on the above written ASP code. Here I create a command object objCmd, and setting the parameters. The name of the returned variable is "@count_authors", which is also mentioned in the stored procedure. The type of this variable is integer with length 4. When I execute the SP, sp_sel_no_authors, I get the return value, as output which can be displayed in the web page. I hope this two examples are sufficient enough for ASP programmers. Now let us turn to VB.
For example if you pass the date as a variable called Orderdate, Then add the following lines of code in your stored procedure,
SELECT @Orderdate=CONVERT(datetime, @Orderdate)
Here we convert the date string into datatime data type. In this way you can manipulate dates.
If a stored procedures that do not return records (or rows) can be executed from Visual Basic with the ExecuteSQL() method as follows. That means we can not use this method with SELECT statements. But if the the SQL statement returns records then we need to use a Dynaset or Snapshot to capture the values. The following returns a set of recordset values using a Data control.
objDC.Recordsource = "sp_sel_no_authors"
objDC.Refresh
Another example can give you better understanding with stored procedures that return values and that not return values.
Dim lng as Long
Dim objRS as Recordset
Set objDB = DBEngine.Workspaces(0).OpenDatabase("", False, False,"ODBC;_ DSN = macdsn;uid=test;pwd=test:") ' For SPs that don't return rows.
lng = Db.ExecuteSQL("YourSP_Name") ' SP which return rows.
Set ObjRS = Db.OpenRecordset("YourSP_Name", dbOpenSnapshot, _
dbSQLPassThrough)
Column1.text = objRS(0) ' Column one
Column2.text = objRS!ColumnName ' Column two
Column3.text = objRS("ColumnName")' Column three
In this section we will cover the necessary information you need to know about finding and dealing errors in a stored procedure during the time of execution. In fact there are about 3800 SQL server error messages, which are maintained in the master catalog's "sysmessages" table. Every error message has its own severity level and it ranges from 0 to 25, depending on how bad the error is.
There are two types of errors you can face when you execute a stored procedure in a SQL server. One is Fatal and another one is nonfatal. Fatal errors normally terminates the execution of the SP and terminates the connection between the SQL server and the client application while nonfatal errors do not.
Here is an example for Fatal error, I am trying to execute a SELECT SQL statement in a table which does not exist.
SELECT * FROM empDB
PRINT 'Table Does not exist.'
GO
As this table doesn't exist the SQL server raises a Fatal Error and the execution of the procedure terminates. So the PRINT statement will not be executed and we will not get the error message 'Table Does not exist'. Instead we get the error message raised by the Server.
Server: Msg 208,Level 16,State 1,Procedure sp_Fatal,Line 3 Invalid object name 'empDB'.
With one example the nonfatal errors can be illustrated. Let us assume that you are trying to Insert NULL value to a filed which is designed a Primary key. This will raise a non fatal error and will allow you to execute the entire procedure.
In three ways you can get the catch the errors in Stored procedures. Using @@ERROR, SP_ADDMESSAGE, and RAISERROR functions within SQL the Server. Let us discuss the one by one.
@@ Error method:The @@ERROR system function returns 0 if the last procedure executed successfully; if the statement generated an error, @@ERROR returns the error number. The following example explains with an Insert statement,
@empId varchar(10),@empName varchar(40),@phone char(12),@address varchar(40) = NULL,
@city varchar(20) = NULL,@state char(2) = NULL,@zip char(5) = NULL
AS
INSERT INTO tblEmployee
(fldEmpId, fldEmpName, fldPhone, fldAddress, fldCity, fldState, fldZip) values (@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)
IF @@ERROR <> 0
BEGINELSE
PRINT "An error occurred while adding the new Employee information"
RETURN(99)
END
BEGINGO
PRINT "The new author information has been loaded"
RETURN(0)
END
In this example the IF...ELSE statements test @@ERROR after an INSERT statement which inserts the employee details in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating the success or failure of the procedure.
Using SP_ADDMESSAGE :SP_ADDMESSAGE is a system stored procedure used to add a new error message to the sysmessages table. This message could be a custom defined one. The syntax for this stored procedure is,
[@severity =] severity,
[@msgtext =] 'msg'
[, [@lang =] 'language']
[, [@with_log =] 'with_log']
[, [@replace =] 'replace']
Here, [@msgnum =] msg_id is the ID of the message; [@severity =] is the severity level of the error (severity is smallint) and value varies from 0-25 as mentioned earlier; [@msgtext =] 'msg' is the text of the error message; [@lang =] 'language' is the language for this message, which helps to display the error message in multiple languages. [@with_log =] is whether the message is to be written to the Microsoft® Windows NT® application log when it occurs, the value will be true or false. [@replace =],If specified as the string REPLACE, an existing error message is overwritten with new message text and severity level.
An example which adds a new error message is,N'Give perscentage value betwwn 1 to 10
Please reexecute with a more appropriate value.'
The RAISERROR method:
Though you can use print statements, RAISERROR is a more powerful statement than PRINT, for returning messages back to applications. In two ways RAISERROR can return messages.
1. Using sp_addmessage a user-defined error message has been added to master.dbo.sysmessages
2. Using the message string specified in the RAISERROR statement.
The advantages of using RAISERROR over PRINT is it can assign a specific error number, severity, and state. Moreover the error can be logged.
[, argument1 [, argument2]])
[WITH options]
Hope we have discussed sufficiently, to start with stored procedures. To practice more, you can expand your applications with Indexes, Triggers etc.,. Meanwhile, we will cover SPs in another article for ASP.NET and VB.NET developers. Write your comments to improve this article to .
