Saturday, April 25, 2009

Web Development and Design - Begining with SQL

Creating  Stored Procedures- Basics

 For this, you should have the basic knowledge on creating queries in SQL(visit http://www.w3schools.com/sql/default.asp).

Wat is really a stored procedure?

It is more like making the sql statements programmable! These stored procedures are stored in the database. It can take parameters and return what you want. Then you can easily call this procedure from a remote application, and get your result. It begins with the keyword create procedure

ex1-
Here is an example for the select query.
(If you want the whole records in the table)

create procedure MyStoredProcedure1
as
select from [TableName] 

ex2-
Here is an example for the select query with input parameters. (If  you want a particular row / set of row- using the where condition)

Here, the input parameter will be given by the user which is dynamic.But, when using a SQL statement we write the where clause like,
select * from [TableName] where [columnName] = 23. The value 23 should get changed according to what user inputs. For that we can write a procedure like ,
 
create procedure MyStoredProcedure2
(
/*input parameters with their type*/
@para1 varchar(50)
)
as
select * from [TableName] where [columnName] = @para1

Here the type of the parameter given should be the type of its corresponding [columnName].

In the next lesson we will look into more examples on stored procedures.
Good Day.