What is stored procedure?
Stored Procedure is a set of SQL statements or queries with
name that are store and manage in database. It is compile queries that can be
useful to manipulate the data with fast processing to the end users.
Example:
- following are the examples
1:- Example for insert
statements
create
procedure sp_your_proc_name
(
@customerId int,
@customerName varchar(50),
@customerAge
int,
@customerCity Varchar(50)
)
as begin
insert into tableName(customerId, customerName, customerAge , customerCity) values(1,’alok’,30,’Noida’)
end
2:- Example for SELECT statements
Create Proc sp_your_proc_name
(
@intput_parameter1 datatype,
@intput_parameter1 datatype
)
as
begin
Select column1, column2,
column3…..
from tableName
where condtion_based_on_your_input_parameter
end
Advantages
of Using Stored Procedures
1
- Easy to maintain Stored Procedures because it is stored in specific location.
- Stored Procedures can be tested independent from application and easy to troubleshoot the block of code.
- Stored Procedures is Isolated from Business Rules
- Stored procedures are cached on the server, so it is speed up and optimize query.
- It is Utilization of Set-based Processing
How
to Create a Stored Procedure?
You can create stored procedures via two ways. I have
explained both ways in below:
1:- Using SQL Server
Management Studio
To create a procedure
in Object Explorer
- Connect to an instance of Database Engine and then expand that instance in Object Explorer.
- Expand Databases, expand the database in which you want to create procedure, and then expand Programmability.
- Right-click Stored Procedures and then click New Stored Procedure.
- On the Query menu, click Specify Values for Template Parameters.
- In the Specify Values for Template Parameters dialog box, enter the Author name, Create Date,Description,Procedure_name,@Param1 etc. values for the parameters
- Click OK.
- Replace the SELECT statement
with the following statement in the Query Editor,:
Transact-SQL
SELECT Id, Name, Age, Address,
Department, Gender
FROM [SQLCLR].[dbo].[Employee]
- F5 to Execute or click Execute.
The procedure is created as an object in the database.
- To see the procedure listed
in Object Explorer, right-click Stored Procedures and
select Refresh.
- To run the procedure, Open Object Explorer, right-click the stored procedure that you created with assigned name and select Execute Stored Procedure.
2:- Using Transact-SQL
1.
In Object Explorer,
connect to an instance of Database Engine.
2.
From the File menu, click New
Query.
3.
Copy and paste the following example into the query window and
click Execute. This
example creates the same stored procedure as above using a different procedure
name.
USE Payroll;
GO
Create PROCEDURE [dbo].[SP_GetStudents]
AS
BEGIN
SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
FROM dbo.Students
END
4.
To run the procedure, copy and paste the following example into
a new query window and click Execute. Notice
that different methods of specifying the parameter values are shown.
EXEC [dbo].[SP_GetStudents]
5.
Display result.
How
to modify a stored procedure?
Same as
there is two ways to modify the stored procedure. Following, i am explaining
both ways.
1:- Using SQL Server
Management Studio
To
modify a procedure in Management Studio
1. Connect
to an instance of Database Engine and then expand that instance in Object
Explorer.
2. Expand Databases,
expand the database in which the procedure belongs, and then expand Programmability.
3. Expand Stored
Procedures, right-click the procedure to modify, and then click Modify.
4. Modify
the text of the stored procedure.
5. To
test the syntax, on the Query menu, click Parse.
6. To
save the modifications to the procedure definition, on the Query menu, click Execute.
7. To
save the updated procedure definition as a Transact-SQL script, on the File menu, click Save
As. Accept the file name or replace it with a new name, and
then click Save.
2:- Using Transact-SQL
To
modify a procedure in Query Editor
1. Connect
to an instance of Database Engine and then expand that instance in Object
Explorer.
2. Expand Databases;
expand the database in which the procedure belongs. Or, from the tool bar,
select the database from the list of available databases. For this example,
select the Payroll database.
3. On
the File menu, click New
Query.
4. Copy
and paste the following example into the query editor.
USE [Payroll]
IF OBJECT_ID ( 'dbo.SP_GetStudents', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.SP_GetStudents;
GO
CREATE PROCEDURE dbo.SP_GetStudents
WITH EXECUTE AS CALLER
AS
BEGIN
SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
FROM dbo.Students
END
5. On
the File menu, click New
Query.
6. Copy
and paste the following example into the query editor
USE [Payroll]
ALTER PROCEDURE [dbo].[SP_GetStudents]
AS
BEGIN
SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
FROM dbo.Students
END
7. To
save the modifications to the procedure definition, on the Query menu, click Execute.
8. To
save the updated procedure definition as a Transact-SQL script, on the File menu, click Save
As. Accept the file name or replace it with a new name, and
then click Save.
9. To
run the modified stored procedure, execute the following example.
How
to call a function in Procedure?
Step1: create a
function
create
function fn_SumOftwoNumber(@NumIst int,@NumpIInd int)
returns int
as begin
Declare @Result as int
Set @Result=@NumIst+@NumpIInd
return @Result
end
Step2: Call a function in store procedure
create procedure sp_Test_Callfunction
(
@IstVar int,
@IIndVar int
)
as begin
Declare @sp as int
set @sp=dbo. fn_SumOftwoNumber (@IstVar,@IIndVar)
print @sp
end
Step3: Execute Store
procedure for Output
EXEC sp_Test_Callfunction
45,60
No comments:
Post a Comment