ایجاد Stored procedure با پارامترهای داینامیک
در این مقاله از سری مقاله های آموزشی SQL Server به نحوه ایجاد یک رویه یا Stored procedure با پارامترهای داینامیک یا پویا میپردازیم. مزیت اینگونه از پروسیجر ها در این است که میتوان تعداد زیادی پارامتر ورودی تعریف کرد و به تعداد دلخواه از آنها استفاده کرد. ورود مقدار اولیه در پارامترهای ورودی تماما الزامی نیستند و بسته به نیاز می توان تعدادی از آنها را مقدار دهی کرد و خروجی در صورت وجود واکشی و نمایش داده خواهد شد. بدنه قابل اجرای پروسیجر بر اساس پارامترهای ورودی ایجاد و سپس با استفاده از sp_executesql اجرا می شود.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: A. Najafzadeh(+98)913-125-3620
-- Date: 2021-01-17
-- Description: از این پروسیجر برای نمایش گزارش مرخصیها استفاده می شود
-- =============================================
CREATE PROCEDURE [dbo].[SptblAbsentsForReportTotalSum]
@DateStartFrom Date
,@DateEndTo Date
,@PersonnelID BIGINT = NULL
,@CompanyID INT = NULL
,@AbsentTypeID SMALLINT = NULL
AS
BEGIN
SET NOCOUNT ON;
Declare @Sql Nvarchar(Max),
@Paramlist Nvarchar(Max),
@AllColumn Nvarchar(max)
set @Sql = N' SELECT
PersonnelID,
AbsentTypeID,
CompanyID,
DiensNummer,
VorName,
NachName,
AbsentDescription,
SUM(AbsentDays) AS ToralDays ,
CompName '
SET @Sql += N' From [dbo].[XtblAbresntsForReport] Where 1 = 1 '
SET @Sql += N' and
(
(DateFrom BETWEEN @DateStartFrom AND @DateEndTo ) OR
(DateTo BETWEEN @DateStartFrom AND @DateEndTo) OR
(DateFrom <= @DateStartFrom AND DateTo >= @DateEndTo)
)
'
If Not @PersonnelID Is Null
Set @Sql += N' And @PersonnelID = PersonnelID '
If Not @CompanyID Is Null
Set @Sql += N' And @CompanyID = CompanyID '
If Not @AbsentTypeID Is Null
Set @Sql += N' And @AbsentTypeID = AbsentTypeID '
set @Sql += N' GROUP BY PersonnelID, AbsentTypeID, CompanyID, DiensNummer, VorName, NachName, AbsentDescription, CompName '
Set @Paramlist = '
@DateStartFrom Date
,@DateEndTo Date
,@PersonnelID BIGINT = NULL
,@CompanyID INT = NULL
,@AbsentTypeID SMALLINT = NULL'
Exec sp_executesql @Sql, @Paramlist,
@DateStartFrom
,@DateEndTo
,@PersonnelID
,@CompanyID
,@AbsentTypeID
END