Store procedure is like a function in regular programming
language, it accept input from users as input parameters and return result set
or just single scalar value and with the exception of output parameters which
are used for different purposes (showing database related errors to calling
function …etc). Store procedure is a best way to perform database tasks in SQL
SERVER and ADO.NET application (web or desktop application).
Store procedure is secure inside complex security of SQL
SERVER. SQL SERVER thinks store procedure as database object and all the
security futures that are available for a database table will also be applied
on it that which users have access to that store procedure and which are not.
User does not need to know the underline database table design to which store
procedure operate on just like SQL SERVER system store procedures, it isolate
users from the complexity of it. All he need to know what its input values are
and what result will come relative to inputted data.
Performance
CREATE PROCEDURE dbo.storeProcedure_name
-- parameters list
AS
SELECT * FROM dbo.tableName
SQL SERVER uses “CREATE PROCEDURE” keyword to create store
procedure along with schema name and dot and name of procedure. Do not get
confuse with database schema (contains information how internal structure of
database is created like relation between tables and indexes information and so
on.) which is completely different than the schema. Schema is like a logical
container (or like folder or directory in windows) with different permissions
assigned to it though every database object has permissions.
Name of store procedure follows the same rules as naming a
variable. No numbers are come first expect underscore no special symbols are used
and so on.
Parameters list contains many parameters each of them is
separated by comma as you require some of them are optional and others are
required. Optional parameters as name suggest would not heart or generate error
and stop execution on calling store procedure that have them if not specified,
but of the other require parameters make program angry to generate error. So
these require some help to keep them ease.
Body of store procedure goes after “AS” clause just like
function body where logic or reusable code reside, return data decision is also
made there, result set (collection of rows) or just scalar value. Usually store
procedure return result set (collection of rows), but there are some situation
where scalar value works just fine like in inserting, deleting and updating
record with store procedure.
Simple Store Procedure
Before creating a store procedure to perform some useful
works, let first create “movie” table so that we perform practices on. Movie
table contains few columns for practice purpose and nothing special (primary
keys, foreign key constraints, …).
movie (m_id, m_name, m_discription)
CREATE TABLE
dbo.movie(
m_id INT
IDENTITY(1,1)
,m_name VARCHAR(50)
,m_discription VARCHAR(100)
)
Simple store procedure as its name suggests that no
parameters list, performing error handling, checking users input, transaction
handling and so on. It just contains simple “SELECT” statement in its body to
return result set, so that we know the basic skeleton of it.
CREATE
PROCEDURE dbo.getMovies(
-- empty parameter list
AS
-- nothing special here
SELECT m_id, m_name,
m_discription FROM dbo.movie
)
No comments:
Post a Comment