نظر در مورد sp

[Iran58]

سلام
لطفا نظر خودتان را مورد اینگونه sp نوشتن بیان بفرمایید(نقاطضعف وقدرت)
ALTER Procedure [dbo].[S_Personnel_SelectList]
/* Input Parameters */

@Sex tinyint,
@BCID varchar(10),
@BirthDate varchar(10),
@BirthCity nvarchar(100),
@Allegiance nvarchar(100),
@Address nvarchar(100),
@Tel varchar(50),
@MobileNo varchar(50),
@OrgChartID varchar(50),
@OrgChartName varchar(100),
@JobId varchar(50),
@JobName nvarchar(100),
@PositionName nvarchar(100),
@Modiriat nvarchar(100),
@Edare nvarchar(100),
@Bakhsh nvarchar(100)

AS
Set NoCount ON
/* Variable Declaration */
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
/* Build the Transact-SQL String with the input parameters */
Set @SQLQuery = 'Select * From V_PersonnelList where (1 = 1) '
/* check for the condition and build the WHERE clause accordingly */

If @Sex is not null
Set @SQLQuery = @SQLQuery + ' And (Sex = @Sex)'

If @BCID <> ''
Set @SQLQuery = @SQLQuery + ' And (BCID like' + '''%''' + '+ @BCID +' + '''%''' + ')'

If @BirthDate <> ''
Set @SQLQuery = @SQLQuery + ' And (BirthDate = @BirthDate)'

If @Allegiance <> ''
Set @SQLQuery = @SQLQuery + ' And (Allegiance like' + '''%''' + '+ @Allegiance +' + '''%''' + ')'

If @OrgChartName <> ''
If @Address <> ''
Set @SQLQuery = @SQLQuery + ' And (Address like' + '''%''' + '+ @Address +' + '''%''' + ')'

If @Tel <> ''
Set @SQLQuery = @SQLQuery + ' And (Tel like' + '''%''' + '+ @Tel +' + '''%''' + ')'

If @MobileNo <> ''
Set @SQLQuery = @SQLQuery + ' And (MobileNo like' + '''%''' + '+ @MobileNo +' + '''%''' + ')'





/* Specify Parameter Format for all input parameters included
in the stmt */
Set @ParamDefinition = '
@Sex tinyint,
@BCID varchar(10),
@BirthDate varchar(10),
@BirthCity nvarchar(100),
@Allegiance nvarchar(100),
@Address nvarchar(100),
@Tel varchar(50),
@MobileNo varchar(50)

/* Execute the Transact-SQL String with all parameter value's
Using sp_executesql Command */
Execute sp_Executesql @SQLQuery,
@ParamDefinition,
@Sex,
@BCID,
@BirthDate,
@BirthCity,
@Allegiance,
@Address,
@Tel,
@MobileNo

If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)

ErrorHandler:
Return(@@ERROR)

[tooraj_azizi_1035]

سلام
به گفته Kimberly Tripp (http://sqlskills.com/blogs/kimberly) در این پست (http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/) چون شما از sp_Executesql برای اجرا استفاده کردید یک پلن در اجرای اول تولید شده و دفعات بعد هم از همین پلن استفاده می شود (که باعث بهبود Perfromance می شود) البته اگر رشته کوئری همان رشته باشد (يعني در شرط WHERE آن ستون هاي جديد ظاهر نشوند) در غیر این صورت یک پلن جدید ساخته خواهد شد. به طور کلی استفاده از sp_Executesql راهكار مناسب تري است.

[SabaSabouhi]

سلام
از نظر سلیقه‌ای من این رو نمی‌پسندم.
اول این که ترجیح می‌دم بجای SP تو لایه‌ی BL برنامه‌ام این نوع کارها رو انجام بدم.
دوم این که اگر هم می‌خواستم تو SP این نوع کارها رو انجام بدم، ترجیح می‌دادم SPهای ساده داشته باشم که هر کدوم یک کار انجام بدن.
به ویژه‌ که می‌شه این نوع SPها رو حتا با CodeGenerator ایجاد کرد.

صبا صبوحی

[s.karim]

چرا انقدر کارو سخت کردین
این روش خیلی ساده تر و منطقی تره:

ALTER Procedure [dbo].[S_Personnel_SelectList]
/* Input Parameters */

@Sex tinyint,
@BCID varchar(10),
@BirthDate varchar(10),
@BirthCity nvarchar(100),
@Allegiance nvarchar(100),
@Address nvarchar(100),
@Tel varchar(50),
@MobileNo varchar(50),
@OrgChartID varchar(50),
@OrgChartName varchar(100),
@JobId varchar(50),
@JobName nvarchar(100),
@PositionName nvarchar(100),
@Modiriat nvarchar(100),
@Edare nvarchar(100),
@Bakhsh nvarchar(100)


AS
begin

Select
*
From
V_PersonnelList
where
(
@Sex is null
or
@Sex = ''
or
Sex = @Sex
)

And
(
@BCID is null
or
@BCID = ''
or
BCID like '%' + @BCID + '%'
)

And
(
@BirthDate is null
or
@BirthDate = ''
or
BirthDate = @BirthDate
)

And
(
@Allegiance is null
or
@Allegiance = ''
or
Allegiance like '%' + @Allegiance + '%'
)

And
(
@Address is null
or
@Address = ''
or
Address like '%'+ @Address + '%'
)

And
(
@Tel is null
or
@Tel = ''
or
Tel like '%' + @Tel + '%'
)

And
(
@MobileNo is null
or
@MobileNo = ''
or
MobileNo like '%' + @MobileNo + '%'
)
end

[ASKaffash]

با سلام
دو موضوع مهم است :
1- وقتی از Dynamic SQL برای تولید SQL استفاده می کنید که شرط رشته ای در آن وجود دارد همیشه باید مراقب حمل SQL Injection باشید
2- اگر برای خالی بودن یک عبارت یاشرط از Or استفاده می کنید و نمی خواهید از Dynamic SQL استفاده کنید در حجم زیاد داده ها باید نگران کاهش سرعت باشید

[Iran58]

با سلام
دو موضوع مهم است :
1- وقتی از Dynamic SQL برای تولید SQL استفاده می کنید که شرط رشته ای در آن وجود دارد همیشه باید مراقب حمل SQL Injection باشید
2- اگر برای خالی بودن یک عبارت یاشرط از Or استفاده می کنید و نمی خواهید از Dynamic SQL استفاده کنید در حجم زیاد داده ها باید نگران کاهش سرعت باشید
سلام استاد وباسپاس از راهنمای شما
من جدولی را که دارای 27ستون و96000داده است را با کوئری خودم وکوئری مهندس عزیز s.karim (http://barnamenevis.org/member.php?337311-s-karim)
اجرا کردم وهرکدام در مدت 2ثانیه اجرا شد
منظور شما از تعدادبالا حدودا چند رکورد می باشد وسرعت چقدر تغییر می کند

[Iran58]

به طور کلی نمی توان گفته که استفاده از sp_Executesql نادرست است بلکه باید تفاوت های sp_Executesql و EXEC را بررسی کنید.
تفاوتها را چگونه می توانم بررسی کنم
باسپاس

[tooraj_azizi_1035]

EXEC اجازه Parameterize کردن پلن Cache شده رو نمی ده و در نتیجه اونها از نظر SQL Server کوئری های Adhoc محسوب می شن و فقط زمانی Plan اونها دوباره استفاده میشه که دقیقاَ همون کوئری با همون مقادیر رو اجرا کنید.
اما sp_executesql پلن رو به صورت Parameterize شده ذخیره می کنه که فرصت Reuse رو با هر مقداری می ده پس sp_executesql گزینه بهتری است.

یک لینک عالی:
https://technet.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

[ASKaffash]

سلام
زیر یک میلیون رکورد را کوچک تلقی کنید و زیاد چای نگرانی نیست


سلام استاد وباسپاس از راهنمای شما
من جدولی را که دارای 27ستون و96000داده است را با کوئری خودم وکوئری مهندس عزیز s.karim (http://barnamenevis.org/member.php?337311-s-karim)
اجرا کردم وهرکدام در مدت 2ثانیه اجرا شد
منظور شما از تعدادبالا حدودا چند رکورد می باشد وسرعت چقدر تغییر می کند