Передача набора строк в хранимую процедуру:
Вам необходимо многократно передавать табличный результат в хранимую процедуру. Несмотря на то, что SQL Server 2000 и SQL Server 2005 имеют тип данных, который называется табличной переменной, Вы не можете использовать ее как параметр в хранимых процедурах. Начиная с версии SQL 2000, Microsoft все в большей мере поддерживает XML. Я знаю, что большинство из Вас уже использовало XML или, по крайней мере, много слышало о XML; однако я не хочу углубляться в XML, а лишь хочу показать Вам варианты его использования. Для тех из Вас, кто не знаком с XML, следующая ссылка даст хорошее введение в предмет: www.topxml.com/sql/learn_sql_server_xml_tutorial.asp
Итак, начнем. Пусть имеется следующая таблица:
CREATE TABLE Employee (empid INT,
name VARCHAR (20),
salary MONEY NOT NULL
)
И мы хотим вставить следующий список сотрудников в таблицу employee:
Empid Name Salary
1 John $10000.00
2 Joseph $3000.00
3 Melissa $2500.00
4 Dan $2000.00
Для этого мы создадим хранимую процедуру, чтобы выполнить обработку XML. Новый тип данных XML, введенный в SQL 2005, является наиболее подходящим для того, чтобы хранить значения XML. Вместо этого Вы можете также использовать VARCHAR (MAX) или, если Вы используете SQL 2000, то VARCHAR (8000).
Create PROC usp_ProcessRowset @p XML
AS
SET NOCOUNT ON
DECLARE @ih INT
EXEC sp_XML_preparedocument @ih output, @p
INSERT Employee
SELECT *
FROM OPENXML(@ih, 'data/emp')
WITH Employee
EXEC sp_XML_removedocument @ih
Следующий шаг должен изменить входные данные на допустимый XML. Просто объявим переменную XML и присвоим ей желаемые XML-данные, а затем выполним вышеприведенную хранимую процедуру:
declare @x XML
set @x='
<emp empid="1" name="John" salary="10000" />
<emp empid="2" name="Joseph" salary="3000" />
<emp empid="3" name="Melissa" salary="2500" />
<emp empid="4" name="Dan" salary="2000" />
</data>'
exec usp_ProcessRowset @x
-- Только для того, чтобы удостовериться, что это работает
select * from Employee
Вот результат:
Empid Name Salary
1 John $10000.00
2 Joseph $3000.00
3 Melissa $2500.00
4 Dan $2000.00
(4 row(s) affected)
Вы можете собирать пользовательские входные данные в файле XML, и впоследствии передавать его в хранимую процедуру. При использовании этой техники Вы минимизируете вызовы хранимой процедуры, повышая производительность.
Извлечение значений из списка, разделенного запятыми (CSV)
Несколько раз на форумах я видел сообщения от новых разработчиков SQL, которые спрашивали, как они могут передать массив в процедуру. На мой взгляд, единственным ответом на тот вопрос является использование списка с запятой-разделителем; однако его обратное преобразование к набору строк - это головная боль. Некоторые разработчики будут использовать цикл для извлечения значений из списка. В этой статье я покажу Вам новый способ решения этой проблемы.
Решение циклом:
Итак, начнем. Пусть имеется следующая таблица:
create proc usp_LoopVersion @CSV varchar(max)
AS
SET NOCOUNT ON
declare @res table (val varchar(100))
declare @pos int
set @pos=CHARINDEX(',',@CSV,1)
while @pos > 0
begin
insert @res
values(ltrim(substring(@CSV,1,@pos-1)))
set @CSV=right(@CSV,len(@CSV)-@pos)
set @pos=CHARINDEX(',',@CSV,1)
end
insert @res
values(ltrim(@CSV))
select *
from @res
Здесь просто определяется положение первой запятой, затем извлекается первое значение списка (от начала списка до позиции найденной запятой) и добавляется в табличную переменную @res; после чего добавленное значение удаляется из списка. Этот алгоритм повторяется в цикле до тех пор, пока в списке обнаруживается запятая.
Решение XML:
CREATE PROC usp_LoopVersion @CSV VARCHAR(MAX)
AS
SET NOCOUNT ON
DECLARE @idoc INT
--Раздел A
SET @CSV ='<root><x val="'+replace(@CSV,',','" /><x val="')+'" /></root>'
EXEC sp_XML_preparedocument @idoc OUTPUT, @CSV
SELECT *
FROM OPENXML (@idoc, '/root/x',1)
WITH (val VARCHAR (100) )
EXEC sp_XML_removedocument idoc
Я заменил запятые на " /><x val=", поэтому в Разделе A @CSV будет представлять допустимое выражение XML. После этого я вызываю sp_XML_preparedocument для получения обработчика документа, который затем передается в OPENXML. Вызов sp_XML_removedocument - критический момент. Без этого документ останется в памяти.
Для тестирования хранимых процедур запустите следующий пример кода:
DECLARE @i int
DECLARE @CSV varchar(max)
set @i=0
while @i < 2
begin
SET @CSV=isnull(@CSV+',test','test ' )
set @i=@i+1
end
print @CSV
exec usp_LoopVersion @CSV
exec usp_LoopVersion @CSV
Отметим, что Вы можете использовать любой разделитель вместо запятой или, если Вам нравится, то можете передавать разделитель как параметр, - пример, который я оставляю Вам. Вы также можете загрузить код хранимой процедуры отсюда: code.txt
Производительность:
На моей машине со списком из 10 000 разделенных запятыми значений, версия XML управлялась на 3 секунды быстрее, чем версия с циклом.
Заключение:
XML в SQL Server 2005 получил дальнейшее развитие, и Microsoft добавила новые возможности, например, тип данных XML и спецификатор MAX. Я попытался показать Вам некоторые варианты использований XML, которые могут оказаться практичными в реальном мире. Я надеюсь, что эта статья дала вам новые знания.
Yousef Ekhtiari (оригинал: Some Usages for XML)
Перевод: Моисеенко С.И.
Оригинал перевода