Microsoft defines a stored procedure as follows:
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. You need a bunch of things to understand this.
Wikipedia describes it as follows:
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary. This is somewhat simpler to understand.
What I plan to show you here is how a stored procedure is written and how you can use it. In a relational database system you would be using the data from a table or tables. Herein however, I will be explaining in very simple terms but still using the Microsoft SQL Server's database engine.
Example 1:
The first stored procedure is really simple. There are two steps to it. In the first step you create a stored procedure and in the second step you call it.
The stored procedure in this example just echos the value you call the stored procedure with.
1. Create stored procedure:
Create proc sumr @x int
1. Create proc sumr creates a stored procedure called sumr
2. Next you have to use an argument that you will be calling the procedure later and this is @x (a variable). You should specify what its data type is. Here we just say a variable x which is an integer
3. Now this procedure just displays the variable when called.
Important to note is the syntax, you must write it as shown in the code above.
When you run the above statements in the query designer of Microsoft SQL Server Management Studio(SSMS), the database engine creates the stored procedure and keeps it in its storage.
2. Run (Execute) the stored procedure:
This is very simple and goes like this,
Execute sumr 24
You can also use Exec or exec instead of Execute.
The result produced is 24. You call it with a value of 75 and the result will be 75.
Example 2
The next example is also simple. You might have learnt in elementary algebra an equation shown below:
(x+y)^2=x^2+2*x*y+y^2
We could write a stored procedure for this as shown below.
Create Proc sumr3 @x int, @y int
Here the name of the procedure is sumr3 and it takes two variables x and y and produces the value (x+y)^2
You run the above statement and create the stored procedure sumr3.
Now how do you call this stored procedure. Easy! You need to provide a value for x and a value for y while executing the procedure.
Here's how you call it,
Exec sumr3 3, 4
You are providing x=3 and y=4 and you are looking to find the value of (3+4)^2 .
Of course a database engine is not for doing algebra and this demo was to show that you create one or more placeholders(variables) and then create a simple or complex expression and the database engine stores this complex expression.
You call the expression by providing value for the placeholders and you get to evaluate the expression. It is to be noted that I am not using any data in the database except the stored procedure I created.
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. You need a bunch of things to understand this.
Wikipedia describes it as follows:
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary. This is somewhat simpler to understand.
What I plan to show you here is how a stored procedure is written and how you can use it. In a relational database system you would be using the data from a table or tables. Herein however, I will be explaining in very simple terms but still using the Microsoft SQL Server's database engine.
Example 1:
The first stored procedure is really simple. There are two steps to it. In the first step you create a stored procedure and in the second step you call it.
The stored procedure in this example just echos the value you call the stored procedure with.
1. Create stored procedure:
Create proc sumr @x int
as
select @x1. Create proc sumr creates a stored procedure called sumr
2. Next you have to use an argument that you will be calling the procedure later and this is @x (a variable). You should specify what its data type is. Here we just say a variable x which is an integer
3. Now this procedure just displays the variable when called.
Important to note is the syntax, you must write it as shown in the code above.
When you run the above statements in the query designer of Microsoft SQL Server Management Studio(SSMS), the database engine creates the stored procedure and keeps it in its storage.
2. Run (Execute) the stored procedure:
This is very simple and goes like this,
Execute sumr 24
You can also use Exec or exec instead of Execute.
The result produced is 24. You call it with a value of 75 and the result will be 75.
Example 2
The next example is also simple. You might have learnt in elementary algebra an equation shown below:
(x+y)^2=x^2+2*x*y+y^2
We could write a stored procedure for this as shown below.
as
Select @x*@x+2*@x*@y+@y*@yHere the name of the procedure is sumr3 and it takes two variables x and y and produces the value (x+y)^2
You run the above statement and create the stored procedure sumr3.
Now how do you call this stored procedure. Easy! You need to provide a value for x and a value for y while executing the procedure.
Here's how you call it,
You are providing x=3 and y=4 and you are looking to find the value of (3+4)^2 .
Of course a database engine is not for doing algebra and this demo was to show that you create one or more placeholders(variables) and then create a simple or complex expression and the database engine stores this complex expression.
You call the expression by providing value for the placeholders and you get to evaluate the expression. It is to be noted that I am not using any data in the database except the stored procedure I created.
No comments:
Post a Comment