Page 221 - Open Soource Technologies 304.indd

Open Source Technologies

Notes • A function can have only IN parameters, while stored procedures may have OUT or
INOUT parameters.
• A stored procedure can return multiple values using the OUT parameter or return no
value at all.
13.2.5 Disadvantages

Stored procedure languages are quite often vendor-specific. If you want to switch to using
another vendor’s database, then you have to rewrite your stored procedures. Stored procedure
languages from different vendors have different levels of sophistication; for example, Oracle’s
PL/SQL has more languages features and built-in features (via packages such as DBMS_ and
UTL_ and others) than Microsoft’s T-SQL. Tool support for writing and debugging stored
procedures are often not as good as for other programming languages; but again, this differs
between vendors and languages (for example, both PL/SQL and T-SQL have dedicated IDEs
and debuggers).
13.2.6 Why Use Stored Procedures?

There are several advantages of using stored procedures instead of standard SQL. First, stored
procedures allow a lot more flexibility offering capabilities such as conditional logic. Second,
because stored procedures are stored within the DBMS, bandwidth and execution time are
reduced. This is because a single stored procedure can execute a complex set of SQL statements.
Third, SQL Server pre-compiles stored procedures such that they execute optimally. Fourth,
client developers are abstracted from complex designs. They would simply need to know the
stored procedure’s name and the type of data it returns.

13.2.7 Creating a Stored Procedure

Enterprise Manager provides an easy way to create stored procedures. First, select the database
to create the stored procedure on. Expand the database node, right-click on “Stored Procedures”
and select “New Stored Procedure...”. You should see the following:

Create procedure [owner].[procedure name] as
Substitute OWNER with “dbo” (database owner) and PROCEDURE NAME with the name of
the procedure. For example:

Create procedure [dbo].[getproducts] as
So far, we are telling SQL Server to create a new stored procedure with the name GetProducts.
We specify the body of the procedure after the AS clause:

CREATE PROCEDURE [dbo].[GetProducts] ASSELECT ProductID, ProductName FROM
Products Click on the Check Syntax button in order to confirm that the stored procedure is
syntactically correct. Please note that the GetProducts example above will work on the Northwind
sample database that comes with SQL Server. Modify it as necessary to suite the database you
are using.
Now that we have created a stored procedure, we will examine how to call it from within a
C# application.

216 LOVELY PROFESSIONAL UNIVERSITY