Stored Procedures for ASP, ASP.NET, VB and VB.NET Programmers

Share |

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.

What are Stored Procedures?
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.

Why write Stored procedures?

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.

What are the types of Stored Procedures?
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.
2. Extended stored procedures:
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.

Where to write Stored Procedures?
You can write Stored Procedures in the following possible ways,
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.

Starting Stored Procedures
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.

CREATE PROCEDURE sp_selauthors AS

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.
All about parameters
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,

CREATE PROCEDURE sp_selauthors 

@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.

Calling Stored procedures from your ASP code
Now let us call the stored procedure from ASP code. I will take stored procedure EXAMPLE2, to explain.

<%
   1: Dim objConn, sqlInsName, FName, LName
   2: FName = "Benny"
   3: LName = "Alexander"
   4: set objConn = Server.CreateObject("ADODB.Connection")
   5: objConn.Open "DSN=macdb;uid=test;pwd=test" 
   6: sqlInsName = "sp_InsName '" & FName & "', '" & LName & "'"
   7: objConn.Execute(sqlInsName) 
%>

The line ObjConn.Excecute runs the SP from the ASP code. Hope you understand how simple it is.
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.

<%
   1:  <!--#INCLUDE VIRTUAL="/include/adovbs.inc"-->
   2: Dim objConn, objCmd, objParam
   3: set objConn = Server.CreateObject("ADODB.Connection")
   4: objConn.Open "DSN=macdb;uid=test;pwd=test" 
   5: Set objCmd = Server.CreateObject("ADODB.Command")
   6: objCmd.CommandText = "sp_sel_no_authors"
   7: objCmd.CommandType = adCmdStoredProc
   8: objcmd.ActiveConnection = objConn 
   9: Set objParam = objCmd.CreateParameter ("@count_authors",adInteger,adParamOutput,4)
  10: objCmd.Parameters.Append objParam
  11: ObjCmd.Execute
  12: <HTML>
  13: <BODY>
  14: 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.

A Tip: You may get an error when you pass date as an input parameter. In that case send the date as a string and convert the string as date inside the SP.
For example if you pass the date as a variable called Orderdate, Then add the following lines of code in your stored procedure,

DECLARE @Orderdate DATETIME

SELECT @Orderdate=CONVERT(datetime, @Orderdate)

Here we convert the date string into datatime data type. In this way you can manipulate dates.

Calling Stored procedures from your VB code
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.Options = dbSQLPassThrough

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 objDB as Database

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

Handling errors in stored procedures
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.

CREATE PROCEDURE sp_Fatal

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,

CREATE PROCEDURE sp_addEmployee

@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 

BEGIN

PRINT "An error occurred while adding the new Employee information"

RETURN(99)

END

ELSE

BEGIN

PRINT "The new author information has been loaded"

RETURN(0)

END

GO

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,

sp_addmessage [@msgnum =] msg_id, 

[@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,

EXEC sp_addmessage 50001, 16, 

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.

The syntax for RAISERROR method is as follows,

RAISERROR ({msg_id | msg_str}, severity, state

[, argument1 [, argument2]])

[WITH options]

Stored Procedures for ASP, ASP.NET, VB and VB.NET Programmers