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 [email protected]
Requirements: Programming knowledge in ASP or VB, MS SQL server & Database concepts.
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.
xp_cmdshell "dir c:\",will list the files in the root directory of C drive.
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.
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,
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.
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.
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.
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,
PRINT "An error occurred while adding the new Employee information"
PRINT "The new author information has been loaded"
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,
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,
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.