تبدیل یک رشته به مجموعه اعداد از دستور IN برای استفاده از یک کوئری در یک Stored Procedure

[behzadkhan]

با سلام

و عرض خسته نباشید به تمام اعضای سایت

سناریو:
فرض کنید ما دو جدول اعضا و گروه به شرح زیر داریم:

tbl_Member



شناسه
نام
نام خانوادگی
شناسه گروه


1
علی
کریمی
1


2
رضا
نریمان جهان
2


3
داود
صبوحی
1


4
سامان
صباغی
1



tbl_Group


شناسه
نام گروه


1
کوه نوردی


2
پیاده روی




حالا ما می خواهیم یک Stored پروسیجر درست کنیم که یک پارامتر داشته باشد
البته می دونم که این کوئری اشتباه هست و نمی تواند اجرا بشود.

create proc Search
(
@GroupIDs nvarchar(50)
)
AS
BEGIN
SELECT * FROM tbl_Member
WHERE GroupID IN (@GroupIDs)
END


در این پارامتر ما امکان این را داشته باشیم که بتوانیم چند شناسه را در یک پارامتر به اون ارسال کنیم.

یعنی اگر مثلا من رشته "1,2,3" را در زبان سی شارپ به عنوان پارامتر این Stored Procedure ارسال کردم. دستور SELECT به شکل زیر در بیاید:


SELECT * FROM tbl_Member
WHERE GroupID IN (1, 2, 3)


حالا من چه یک یا n تا عدد در اون پارامتر به فرمت

1,2,10,4,...

ارسال کردم اونو تشخیص بده و تمام اعضایی که عضو آن گروه ها هستند را بیست کند.

از شما می خواهم stored procedure بنویسید که یک پارامتر داشته باشد و حالت پویا باشد و بتوان در آن یک یا n تا عدد را ارسال کنیم و در کوئزی آن stored procedure آن اعداد را از هم جدا کند و در دستور select قرار دهد.

==============================================

اگر منظورم را متوجه نشدید بگید تا بیشتر توضیح بدهم.

همچنین

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

===============================================

منتظر راه حل های شما هستیم.

موفق و پیروز باشید.

با تشکر

[tooraj_azizi_1035]

سلام
شما بايد يك User Defined Table Type به شكل زير تعريف گنيد:

create type GroupIDs as table (GroupID int)

و به اين شكل SP رو بنويسيد:

CREATE PROCEDURE [dbo].[selectData]
@GroupIDs Groups READONLY
AS
BEGIN
SELECT t.* FROM tbl_Member t
join @GroupIDs g on t.GroupID=g.GroupID
end


و در برنامه يك DataTable را با مقادير پر كرده و به اين SP پاس بديد.

[mjdeveloper]

پارامتر رو بصورت متنی 1,2,3 بفرست
create proc Search(
@GroupIDs nvarchar(50)
)
AS
BEGIN
exec('SELECT * FROM tbl_Member WHERE GroupID IN ('+@GroupIDs+')')
END

[tooraj_azizi_1035]

به دلايلي كه در اين پست توضيح دادم استفاده از exec توصيه نميشه.
http://barnamenevis.org/showthread.php?501695-%D9%86%D8%B8%D8%B1-%D8%AF%D8%B1-%D9%85%D9%88%D8%B1%D8%AF-sp

[mjdeveloper]

CREATE FUNCTION dbo.splitstring (@GroupIDs VARCHAR(MAX) )RETURNS
@returnList TABLE ([GroupID] [int])
AS
BEGIN


DECLARE @GroupID nvarchar(10)
DECLARE @pos INT


WHILE CHARINDEX(',', @GroupIDs) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @GroupIDs)
SELECT @GroupID = SUBSTRING(@GroupIDs, 1, @pos-1)


INSERT INTO @returnList
SELECT Cast(@GroupID as int)


SELECT @GroupIDs = SUBSTRING(@GroupIDs, @pos+1, LEN(@GroupIDs)-@pos)
END


INSERT INTO @returnList
SELECT Cast(@GroupIDs as int)


RETURN
END

Create proc Search(
@GroupIDs nvarchar(50)
)
AS
BEGIN
SELECT * FROM tbl_Member
WHERE GroupID IN (SELECT GroupID FROM dbo.splitstring(@GroupIDs))
END

[behzadkhan]

سلام
شما بايد يك User Defined Table Type به شكل زير تعريف گنيد:

create type GroupIDs as table (GroupID int)

و به اين شكل SP رو بنويسيد:

CREATE PROCEDURE [dbo].[selectData]
@GroupIDs Groups READONLY
AS
BEGIN
SELECT t.* FROM tbl_Member t
join @GroupIDs g on t.GroupID=g.GroupID
end


و در برنامه يك DataTable را با مقادير پر كرده و به اين SP پاس بديد.

با سلام

دوست عزیز

لطفا درباره User Defined Table Type بیشتر توضیح بدهید.

همچنین

چرا دو جدول را با هم Join کرده اید.

با تشکر

[behzadkhan]

CREATE FUNCTION dbo.splitstring (@GroupIDs VARCHAR(MAX) )RETURNS
@returnList TABLE ([GroupID] [int])
AS
BEGIN


DECLARE @GroupID nvarchar(10)
DECLARE @pos INT


WHILE CHARINDEX(',', @GroupIDs) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @GroupIDs)
SELECT @GroupID = SUBSTRING(@GroupIDs, 1, @pos-1)


INSERT INTO @returnList
SELECT Cast(@GroupID as int)


SELECT @GroupIDs = SUBSTRING(@GroupIDs, @pos+1, LEN(@GroupIDs)-@pos)
END


INSERT INTO @returnList
SELECT Cast(@GroupIDs as int)


RETURN
END

Create proc Search(
@GroupIDs nvarchar(50)
)
AS
BEGIN
SELECT * FROM tbl_Member
WHERE GroupID IN (SELECT GroupID FROM dbo.splitstring(@GroupIDs))
END

با سلام

دوست عزیز

آیا دستور زیر


SELECT GroupID FROM dbo.splitstring(@GroupIDs)


در هنگام اجرا


SELECT * FROM tbl_Member
WHERE GroupID IN (SELECT GroupID FROM dbo.splitstring(@GroupIDs))


فقط یکبار اجرا می شود و یا به ازای هر رکوردی مورد مقایسه یکبار اجرا می گردد.

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

اگر اینجور باشد Perfromance کوئری پایین خواهد آمد.

با تشکر

[tooraj_azizi_1035]

خروجي تابع splitstring كه دوستمون براتون نوشتن يكبار در ديتابيس temp به شكل جدولي ساخته مي شود و آن جدول با جدول شما Join مي شود،
و فقط نتيجه اين تابع كه يك جدول است
1000 بار به اسكن مي شود (چون ورودي پاييني در Nested Loops خواهد بود مي توانيد در پلن اجراببينيد).



لطفا درباره User Defined Table Type بیشتر توضیح بدهید.


شما يك نوع داده اي از نوع Table تعريف مي كنيد تحت يك نام و از آن به بعد مي توانيد از آن نوع داده اي در هر جايي كه يك Type ظاهر مي شود استفاده كنيد.




چرا دو جدول را با هم Join کرده اید.


چون قرار است فقط گروه هايي كه پاس داده ايد را در خروجي نمايش دهيم.

[behzadkhan]

با سلام

دوست عزیز

اکنون هر دو روش را متوجه شدم.

======================================

حالا

اگر امکانش باشد

بگویید

User Defined Table Type

را کجا باید تعریف کرد

و

تفاوت های این دو روش از نظر Performance به چه شکلی هست؟

با تشکر

[tooraj_azizi_1035]

به شكل يك اسكريپت:


CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );


از نظر Performance روش TVP بهينه تر است چون محاسبه اي در كار نيست.
تابع Split به روشي غير Set پياده شده است كه اصولاً با زبان TSQL كه مبتني بر SET است ناسازگار است.
هر دو روش از ديتابيس Temp براي ذخيره موقت استفاده مي كنند و تفاوت ديگر در نحوه درج ركورد ها در جدول temp است كه در روش Split اين كار رديف به رديف انجام مي شود اما در روش TVP به شكل bulk-insert به صورت غير علني كه بسيار سريعتر است و سربار كمتري دارد.
بيشتر:
http://www.dbdelta.com/maximizing-performance-with-table-valued-parameters/

[tooraj_azizi_1035]

و يك مزيت ديگه كه فراموش كردم اينه كه استفاده از EXEC فرصت Reuse پلن اجرا رو از ما ميگيره چون با هر بار اجرا يك پلن جديد توليد ميشه اما در روشي كه پاسخ دادم يك پلن ايجاد شده و به دفعات استفاده ميشه.

[behzadkhan]

و يك مزيت ديگه كه فراموش كردم اينه كه استفاده از EXEC فرصت Reuse پلن اجرا رو از ما ميگيره چون با هر بار اجرا يك پلن جديد توليد ميشه اما در روشي كه پاسخ دادم يك پلن ايجاد شده و به دفعات استفاده ميشه.

با سلام

دوست عزیز

خیلی ممنون.
============

در مورد "پلن ها" آیا تاپیکی می تونید معرفی بکنید؟

اگر تاپیکی نیست یک تاپیک جدید برای آن باز کنم؟

با تشکر

[tooraj_azizi_1035]

باز كنيد اما سوالتان را كلي مطرح نكنيد اگر با مثال باشد بهتر است.