stored procedure یا sp چیست + معرفی ۸ قابلیت استور پروسیجر

در این مقاله در مورد Stored Procedure یا استور پروسیجر که به صورت مخفف به آن SP می گوییم و پارامتر ها و قابلیت های آن در sql server  و Asp.Net صحبت می کنیم.

stored procedure چیست؟

sp چیست؟ استور پروسیجر Stored Procedure، یا به زبان فارسی “رویه های ذخیره شده” اشیایی اجرا پذیر در بانک اطلاعاتی SQL Server هستند که شامل یک یا چندین دستور SQL می شوند. این رویه ها میتوانند پارامتر های ورودی و خروجی داشته باشند. همچنین میتوان داخل این رویه ها به زبان SQL برنامه نویسی کرد.

مهم ترین کاربرد این رویه ها ذخیره کردن دستورات Select ،Insert ،Update ،Delete یا ترکیبی از اینها است. زمانی که از stored procedure استفاده شود، این رویه در حافظه ذخیره شده و برای فراخوانی مجدد نیاز به صرف زمان اولیه نیست و کوئری در خواستی با سرعت بالایی به نسبت قبل اجرا می شود.

قابلیت ها و مزایای Stored Procedure

1. Precompiled بودن آنها به این معنی که به زبان ماشین بسیار نزدیک شده و به طور کامل از اول ترجمه نمیشود، این امر باعث افزایش سرعت بارگزاری می گردد.
2. قابلیت ذخیره داشته و میتوان از یک رویه چندین بار در کد نویسی استفاده کرد.
3. دارای ویرایشگر Query می باشد (میتوانید به سادگی یک Query حرفه ای ایجاد کنید).
4. دارای Syntax Parser هست و از خطاهای SQL جلوگیری می کند.
5. دارای امنیت بالایی است و از هک شدن سایت شما توسط SQL Injection در بسیاری موارد جلوگیری می کند.
6. قابلیت درج چندین دستور SQL را پشت سرهم داراست.
7. قابلیت ارسال ورودی و خروجی دارد.
8. قابلیت برنامه نویسی ( شرط، حلقه و . . .) به زبان TransactSQL دارد.

نحوه ساخت stored procedure در sql server

-وارد بانک اطلاعاتی SQL Server شده، پس از باز کردن بانک مورد نظر در قسمت Programmability وارد بخش Stored Procedure شوید.

-بر رویStored Procedure  کلیک راست کرده و New Stored Procedure را انتخاب نمایید.

احتمالا کد های پیشفرضی را در این صفحه مشاهده می کنید، همه این کد ها را حذف کرده و به صورت زیر کد نویسی کنید:

Create Procedure YourProcedureName(@FirstParamete DataType,@SecondParameter DataType,...)
As
Begin
SQLStatement
End

کد فوق شکل کلی ساخت رویه های ذخیره شده می باشد.

به جای YourProcedureName یک نام برای برای رویه انتخاب کنید. سعی کنید نامی که انتخاب می نمایید مفهوم رویه شما را برساند به عنوان مثال برای درج یک رکورد جدید در جدول Members می توانید نام AddNewMember را انتخاب کنید و یا برای دریافت تمام رکورد های این جدول نام GetAllMembers نام با مفهومی است.

در پرانتز مقابل نام رویه پارامتر های رویه به همراه نوع پارامتر تعیین می شود، اگر تعداد پارامتر ها بیشتر از یکی بود با ویرگول آنها را جدا کنید.

به جای SQLStatement  دستورات SQl  را که بین Begin   و End  نوشته می شوند تایپ کنید.

دستوراتی مانندSelect ,Insert ,Update ,Delete   و . . .

به عنوان مثال SP استور پروسیجر زیر اطلاعات کاربر را با دریافت پارامتر نام کاربری ( ( @ID  از جدول TBL_Members  دریافت می کند:

create procedure GetMemberbyID(@ID nvarchar(100))
as
begin
select * from TBL_Members where ID=@ID
end

سپس بر روی دکمه Execute کلیک کرده تا رویه شما ساخته و ذخیره گردد.

نحوه ویرایش رویه های ذخیره شده یا stored procedure

برای ادیت استور پروسیجر می توانید از Alter استفاده کنید:

create procedure GetMemberbyID(@ID nvarchar(100))
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployeeDetails
      @EmployeeID int = 0
AS
BEGIN
      SET NOCOUNT ON;
      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees WHERE EmployeeID=@EmployeeID
END
GO

دستور drop در stored procedure

برای drop از کد زیر استفاده می کنیم:

DROP PROCEDURE GetEmployeeDetails

استفاده از استور پروسیجر در Asp.Net

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

استفاده از این ابزار بسیار ساده هست. اگر از SQL Datasource یا Object Datasource استفاده می کنید یک ویزارد برای ساخت و استفاده از Stored Procedure ها قرار داده شده که به راحتی آن را مدیریت کنید.

اگر از کد نویسی برای کار با بانک های اطلاعاتی استفاده می کنید کافیست بعد از تعریف SQLCommand به جای دستورات SQL نام SP استور پروسیجر را وارد نموده و بعد از آن کد زیر را اضافه نمایید:

Cmd.CommandType = CommandType.StoredProcedure;

این کد به برنامه می گوید که دستورات نوشته شده از نوع Stored Procedure هستند و نیازی به ترجمه آن ها نیست.

در نهایت کد های اتصال ما به شکل زیر خواهد بود:

SqlConnection Con=new SqlConnection("YourConnectionString");
SqlCommand Cmd = new SqlCommand("YourProcedureName", Con);
Cmd.CommandType = CommandType.StoredProcedure;
Con.Open();
// Some Code...
Con.Close();

برای ارسال پارامتر هم به صورت زیر عمل می کنیم:

SqlConnection Con = new SqlConnection("YourConnectionString");
SqlCommand Cmd = new SqlCommand("YourProcedureName", Con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@FirstParam", Textbox1.Text);
Cmd.Parameters.AddWithValue("@SecondParam", Textbox2.Text);
Con.Open();
// Some Code...
Con.Close();

منظور از پارامترهای stored procedure چیست؟

Stored Procedure (استور پروسیجر) دارای دو پارامتر به نام FirstParam و SecondParam است (البته شما باید به شکل صحیحی پارامتر ها را نام گزاری کنید) و از طریق متد AddWithValue  مقدار دهی شده است.