چگونه در SQL Server یک Stored Procedure بسازیم

سلام کاربران عزیز امروز میخواهیم درموزد ساخت پروسیجر کمی بیاموزیم لطقا با ما همراه باشید. پروسیجر در SQL Server  میتواند به عنوان یک سری از گروه های منطقی از مقادیر SQL تعیین شود که گروه بندی شده اند تا وظایف به خصوصی انجام دهند. مزایای زیادی در استفاده از Stored Procedure وجود دارد. مزیت اصلی استفاده از Stored Procedure این است که قابلیت اجرایی پایگاه داده را بالا میبرد.

مزایای استفاده از Stored Procedure

یکی از مزیت های اصلی استفاده از Stored Procedure این است که مقدار اطلاعات فرستاده شده به سرور پایگاه داده را کاهش میدهد. این میتواند تبدیل به مزیت مهمتری شود زمانی که پهنای باند شبکه کم است.

گام کامپایل کردن زمانی موردنیاز است که Stored Procedure ساخته شده باشد. سپس بعد از آن نیازی به کامپایل دوباره قبل از اجرا نیست مگر اینکه از همان اصطلاح و روش های اجرا در جایی که مقدار SQL هربار که برای اجرا فرستاده میشود احتیاج به کامپایل شدن داشته باشد حتی اگر ما یک مقدار یکسان را هربار بفرستیم.

کمک به قابلیت استفاده ی مجدد از کد SQL میکند زیرا میتواند توسط چندین کاربر و چندین مشتری استفاده شود زیرا ما تنها نیاز به فراخوانی Stored Procedure به جای نوشتن مقدار SQL در هربار داریم. این کار کمک به کاهش زمان توسعه میگردد.

Stored Procedure در افزایش حفاظت مفید میباشد از آنجایی که میتوانیم به کاربر اجازه ی اجرای Stored Procedure را بدهیم به جای اجازه ی جداول استفاده شده در Stored Procedure .

گاهی اوقات مفید است که از پایگاه داده برای مرتب سازی کارهای منطقی در فرم Stored Procedure استفاده شود از آنجایی که آن را امن میکند و اگر تغییری در منطق کار موردنیاز بود آنگاه ما تنها نیاز به ایجاد تغییرات در Stored Procedure هستیم و نه فایل های شامل وب سرور.
چگونه یک Stored Procedure در SQL Server بنویسیم؟

فرض کنید که جدولی به نام tbl_Students که ساختارش مانند زیر است داریم :
CREATE TABLE tbl_Students
(

[Studentid] [int] IDENTITY(1,1) NOT NULL,

[Firstname] [nvarchar](200) NOT NULL,

[Lastname] [nvarchar](200) NULL,

[Email] [nvarchar](100) NULL

)
۱
۲
۳
۴
۵
۶
۷
۸
۹
۱۰
۱۱
۱۲

CREATE TABLE tbl_Students
(

[Studentid] [int] IDENTITY(1,1) NOT NULL,

[Firstname] [nvarchar](200) NOT NULL,

[Lastname] [nvarchar](200) NULL,

[Email] [nvarchar](100) NULL

)

ما داده های زیر در جدول بالا وارد میکنیم.
Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Vivek’, ‘Johari’, ‘vivek@abc.com’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Pankaj’, ‘Kumar’, ‘pankaj@abc.com’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Amit’, ‘Singh’, ‘amit@abc.com’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Manish’, ‘Kumar’, ‘manish@abc.comm’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Abhishek’, ‘Singh’, ‘abhishek@abc.com’)
۱
۲
۳
۴
۵
۶
۷
۸
۹
۱۰
۱۱
۱۲
۱۳
۱۴

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Vivek’, ‘Johari’, ‘vivek@abc.com’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Pankaj’, ‘Kumar’, ‘pankaj@abc.com’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Amit’, ‘Singh’, ‘amit@abc.com’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Manish’, ‘Kumar’, ‘manish@abc.comm’)

Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Abhishek’, ‘Singh’, ‘abhishek@abc.com’)

حال درحالی که ما یک Stored Procedure میسازیم گام اول مقدار Create Procedure را به عنوان اولین مقدار مینویسد.
Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement used in the stored procedure
End
۱
۲
۳
۴
۵
۶
۷
۸
۹

Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement used in the stored procedure
End

حال فرض کنید که میخواهیم یک Stored Procedure بسازیم که نام یک Student را که توسط پارامتر ورودی Studentid به آن مقداردهی میشود را برگردانیم. سپس Stored Procedure خواهد بود :
Getstudentname is the name of the stored procedure*/

Create  PROCEDURE Getstudentname(

@studentid INT                   –Input parameter ,  Studentid of the student

)
AS
BEGIN
SELECT Firstname+’ ‘+Lastname FROM tbl_Students WHERE studentid=@studentid
END
۱
۲
۳
۴
۵
۶
۷
۸
۹
۱۰
۱۱

Getstudentname is the name of the stored procedure*/

Create  PROCEDURE Getstudentname(

@studentid INT                   –Input parameter ,  Studentid of the student

)
AS
BEGIN
SELECT Firstname+’ ‘+Lastname FROM tbl_Students WHERE studentid=@studentid
END

همچنین میتوانیم نام Student را در پارامتر خروجی Stored Procedure دریافت کنیم. به عنوان مثال :
/*
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/

Create  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                       –Input parameter ,  Studentid of the student
@studentname VARCHAR(200)  OUT        — Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+’ ‘+Lastname FROM tbl_Students WHERE studentid=@studentid
END
۱
۲
۳
۴
۵
۶
۷
۸
۹
۱۰
۱۱
۱۲
۱۳
۱۴
۱۵
۱۶

/*
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/

Create  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                       –Input parameter ,  Studentid of the student
@studentname VARCHAR(200)  OUT        — Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+’ ‘+Lastname FROM tbl_Students WHERE studentid=@studentid
END

توجه : واجب نیست که Stored Procedure بازگشت داشته باشد. میتواند موردی وجود داشته باشد که Stored Procedure هیچ مقداری را بازنگرداند. به عنوان مثال از یک Stored Procedure میتوان برای افزودن , حذف و یا به روز رسانی یک مقدار SQL استفاده نمود. به عنوان مثال در Stored Procedure زیر برای افزودن یک ارزش به جدول tbl_students استفاده میشود :
/*
This Stored procedure is used to Insert value into the table tbl_students.
*/

Create Procedure InsertStudentrecord
(
@StudentFirstName Varchar(200),
@StudentLastName  Varchar(200),
@StudentEmail     Varchar(50)
)
As
Begin
Insert into tbl_Students (Firstname, lastname, Email)
Values(@StudentFirstName, @StudentLastName,@StudentEmail)
End
۱
۲
۳
۴
۵
۶
۷
۸
۹
۱۰
۱۱
۱۲
۱۳
۱۴
۱۵

/*
This Stored procedure is used to Insert value into the table tbl_students.
*/

Create Procedure InsertStudentrecord
(
@StudentFirstName Varchar(200),
@StudentLastName  Varchar(200),
@StudentEmail     Varchar(50)
)
As
Begin
Insert into tbl_Students (Firstname, lastname, Email)
Values(@StudentFirstName, @StudentLastName,@StudentEmail)
End

اجرای Stored Procedure در SQL Server

اجرای Stored Procedure هایی که پارامتر خروجی ندارند

یک Stored Procedure با کمک کلمات کلیدی “Exec” یا “Execute” در سرور SQL استفاده میشود.

به عنوان مثال اگر بخواهیم که Stored Procedure “Getstudentname” را اجرا کنیم از مقادیر زیر استفاده میکنیم:

Execute Getstudentname 1

Exec Getstudentname 1

اجرای Stored Procedure هایی که پارامتر خروجی دارند

اگر بخواهیم که Stored Procedure “GetstudentnameInOutputVariable” را اجرا کنیم آنگاه ابتدا نیاز به مشخص کردن متغیر ها برای دریافت ارزش های خروجی داریم. برای مثال :
Declare @Studentname as nvarchar(200)   — Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50)     — Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail      — “Select” Statement is used to show the output from Procedure
۱
۲
۳
۴

Declare @Studentname as nvarchar(200)   — Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50)     — Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail      — “Select” Statement is used to show the output from Procedure

جمع بندی

در نهایت میتوانیم بگوییم که Stored Procedure نه تنها قابلیت استفاده ی مجدد از کد و کار اجرا را افزایش میدهد بلکه همچنین اجرای پایگاه داده را با کاهش ترافیک شبکه با کاهش مقادیر اطلاعاتی فرستاده شده در شبکه , افزایش میدهد.