آقای صحراگرد در این مقاله در مورد TVP و کاربرد آن در SQL Server 2008 پرداخته است. در این مقاله روش جایگزینی برای TVP معرفی خواهم کرد. با این فرض که ممکن است شما از نسخه ی 2000 یا 2005 استفاده می کنید.
فرض کنید جدولی داریم که مشخصات افراد در آن درج می شود. و این جدول دارای دو ستون با نامهای، نام کوچک و نام خانوادگی است. قصد داریم SP ای تعریف کنیم که دارای یک پارامتر از نوع رشته ای بوده که توسط آن مشخصات 5 کاربر را بصورت رشته (یکجا) به SP ارسال کنیم تا در جدول کاربران درج شود.
برای اینکار نیاز به یک جدول، یک SP و یک تابع Splitter/Parser داریم.
تابع Splitting
با کمک گرفتن از جدول اعداد می توانیم رشته را تفکیک کنیم، پس بهتر است یک جدول اعداد یکبار برای همیشه ایجاد کنیم تا بتوانیم بارها از آن استفاده کنیم. پس اجازه بدین ابتدا جدول اعداد را ایجاد کنیم. در اینجا تنها 10 هزار سطر در جدول درج خواهیم کرد. تعداد اعداد وابسته به نیاز و سناریوی شما می باشد. بطور مثال برای کار با رشته ها عموما تا 8000 عدد مورد نیاز است.
;WITH C(i) AS
(SELECT '0' UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION
SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION
SELECT '8' UNION SELECT '9')
SELECT C1.i + C2.i + C3.i + C4.i + 1 AS nbr
INTO Nums
FROM C C1, C C2, C C3, C C4;
وقت آن رسیده است که تابع را تعریف کنیم:
CREATE FUNCTION dbo.Splitter (@S NVARCHAR(MAX), @Delimiter CHAR(1)) RETURNS TABLE AS
RETURN (SELECT word
FROM (SELECT CASE WHEN CHARINDEX(@Delimiter, @S + @Delimiter, nbr) - nbr = 0 THEN ''
ELSE SUBSTRING(@S, nbr, CHARINDEX(@Delimiter, @S + @Delimiter, nbr) - nbr)
END, nbr
FROM Nums
WHERE nbr <= LEN(@S)
) d(word, nbr)
WHERE SUBSTRING(@Delimiter + @S, nbr, 1) = @Delimiter);
جدول کاربران
CREATE TABLE Users
(FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL);
Stored Procedure
CREATE PROCEDURE InsertUsers (@string NVARCHAR(MAX)) AS
INSERT INTO Users (FirstName, LastName)
SELECT PARSENAME(word,2) AS FirstName,
PARSENAME(word,1) AS LastName
FROM dbo.splitter (@string, '|');
اکنون تمام پیشنیاز ها را گذراندیم. و می خواهیم 2 سطر را که شامل نام و نام خانوادگی کاربران است به صورت یک رشته ی الحاق شده در بیاوریم و به SP پاس دهیم تا بعد از تفکیک و تجزیه ی رشته و تبدیل آن به سطر، در جدول هدف درج کند.
DECLARE @s NVARCHAR(MAX)
SET @s = STUFF( (SELECT '|' + FirstName + '.' + LastName
FROM (SELECT N'محمد', N'سلیم آبادی' UNION ALL
SELECT N'مرتضی', N'صحراگرد'
) D(FirstName, LastName)
FOR XML PATH('')
), 1, 1, '');
EXECUTE InsertUsers @s;
SELECT * FROM Users
در کد فوق دو سطر مورد نظر با یکدیگر اجتماع شده و سپس با یکدیگر الحاق شده و نهایتا به پروسیجر پاس داده می شود. و با یک دستور SELECT در آخر کار نتیجه ی مورد نظر را مشاهده می کنیم:
محمد سلیم آبادی،
بهترین ها را برایتان آرزو می کنم!