передать таблицу в качестве параметра в udf sql server

<p>я хотел бы передать таблицу в качестве параметра в udf масштабатора. </p><p>я также предпочел бы ограничить параметр таблицами с одним столбцом. (необязательный)</p><p>это возможно?</p><p><strong>правка</strong></p><p>я не хочу передавать имя таблицы, я хотел бы передать таблицу данных (в качестве ссылки я полагаю)</p><p><strong>правка</strong></p><p>я хотел бы, чтобы мой scaler udf в основном брал таблицу значений и возвращал список строк csv.</p><p>ie</p><pre><code>col1 "my first value" "my second value" ... "my nth value" </code></pre><p>вернется</p><pre><code>"my first value, my second value,... my nth value" </code></pre><p>я хотел бы выполнить некоторую фильтрацию таблицы, ie, чтобы убедиться, что нет нулей, и чтобы не было дубликатов. я ожидал что-то вроде:</p><pre><code>select dbo.myfunction(select distinct mydate from mytable where mydate is not null) </code></pre>

<p>к сожалению, в sql server 2005 нет простого способа. ответ лукаша верен для sql server 2008, хотя эта функция<em> длинная </em>просрочена</p><p>любое решение будет включать временные таблицы или передачу xml/csv и анализ в udf. пример: изменить на xml, разобрать в udf</p><pre><code>declare @psuedotable xml select @psuedotable = ... from ... for xml ... select ... dbo.myudf (@psuedotable) </code></pre><p>что вы хотите сделать в целом? может быть другой способ сделать это ...</p><p>правка: почему бы не передать запрос в виде строки и использовать сохраненный процесс с выходным параметром</p><p>примечание: это непроверенный фрагмент кода, и вам нужно подумать о внедрении sql и т.д. однако он также удовлетворяет вашему требованию «одного столбца» и должен помочь вам в этом.</p><pre><code>create proc dbo.tocsv ( @myquery varchar(2000), @csvout varchar(max) ) as set nocount on create table #foo (bar varchar(max)) insert #foo exec (@myquery) select @csvout = substring(buzz, 2, 2000000000) from ( select bar -- maybe cast(bar as varchar(max))?? from #foo for xml path (',') ) fizz(buzz) go </code></pre>

<p>вы можете, однако, не любой стол. из документации:</p><blockquote> <p>для функций transact-sql все данные типы, включая определяемые пользователем clr типы и определяемые пользователем типы таблиц, разрешены, за исключением данных временной метки тип.</p> </blockquote><p>вы можете использовать <a href="http://msdn.microsoft.com/en-us/library/bb522526.aspx"><strong>определяемые пользователем типы таблиц</strong></a> .</p><p>пример пользовательского типа таблицы:</p><pre><code>create type tabletype as table (locationname varchar(50)) go declare @mytable tabletype insert into @mytable(locationname) values('aaa') select * from @mytable </code></pre><p>итак, вы можете определить тип таблицы, например, <code>tabletype</code> и определить функцию, которая принимает параметр этого типа. пример функции:</p><pre><code>create function example( @tablename tabletype readonly) returns varchar(50) as begin declare @name varchar(50) select top 1 @name = locationname from @tablename return @name end </code></pre><p>параметр должен быть чтен. и пример использования:</p><pre><code>declare @mytable tabletype insert into @mytable(locationname) values('aaa') select * from @mytable select dbo.example(@mytable) </code></pre><p>в зависимости от того, чего вы хотите достичь, вы можете изменить этот код.</p><p><strong>edit:</strong> если у вас есть данные в таблице, вы можете создать переменную:</p><pre><code>declare @mytable tabletype </code></pre><p>и взять данные из вашей таблицы в переменную</p><pre><code>insert into @mytable(field\_name) select field\_name\_2 from my\_other\_table </code></pre>

<p><strong> шаг 1 </strong>: создать type as table с именем tabletype, который будет принимать таблицу с одним столбцом varchar</p><pre><code>create type tabletype as table ([value] varchar(100) null) </code></pre><p><strong> шаг 2 </strong>: создать функцию, которая будет принимать выше объявленный tabletype в качестве параметра с табличным значением и строковое значение в качестве разделителя<br /></p><pre><code>create function dbo.fn\_get\_string\_with\_delimeter (@table tabletype readonly,@separator varchar(5)) returns varchar(500) as begin declare @return varchar(500) set @return = stuff((select @separator + value from @table for xml path('')),1,1,'') return @return end </code></pre><p><strong> шаг 3 </strong>: передать таблицу с одним столбцом varchar пользовательскому типу tabletype и ',' в качестве разделителя в функции</p><pre><code>select dbo.fn\_get\_string\_with\_delimeter(@tab, ',') </code></pre>

<p>переходя к нижней строке, вы хотите, чтобы запрос, подобный select x from y, был передан в функцию, которая возвращает значения в виде строки, разделенной запятой.</p><p>как уже было объяснено, вы можете сделать это путем создания типа таблицы и передачи udt в функцию, но для этого нужен многострочный оператор. </p><p>вы можете передавать xml, не объявляя типизированную таблицу, но для этого требуется переменная xml, которая по-прежнему является многострочным оператором, т.е. </p><pre><code>declare @myxml xml = (select x from y for xml raw); select dbo.createcsv(@myxml); </code></pre><p>«for xml raw» заставляет sql выдавать результат в виде некоторого xml.</p><p>но вы можете обойти переменную используя cast (... as xml). тогда это всего лишь вопрос некоторого xquery и небольшого трюка с конкатенацией:</p><pre><code>create function createcsv (@myxml xml) returns varchar(max) begin declare @liststr varchar(max); select @liststr = coalesce(@liststr+',' ,'') + c.value('@value[1]','nvarchar(max)') from @myxml.nodes('/row') as t(c) return @liststr end go -- and you call it like this: select dbo.createcsv(cast(( select x from y for xml raw) as xml)); -- or a working example select dbo.createcsv(cast(( select distinct number as value from master..spt\_values where type = 'p' and number <= 20 for xml raw) as xml)); </code></pre><p>пока вы используете for xml raw, все, что вам нужно, это создать псевдоним нужного столбца в качестве значения, поскольку это жестко задано в функции.</p>

<p>я имел дело с очень похожей проблемой и смог достичь того, что искал, хотя я использую sql server 2000. я знаю, что это старый вопрос, но думаю, что правильно размещать здесь решение, так как должны быть такие, как я, которые используют старые версии и все еще нуждаются в помощи.</p><p>вот хитрость: sql server не будет принимать передачу таблицы в udf, а также вы не можете передать запрос t-sql, поэтому функция создает временную таблицу или даже вызывает хранимую процедуру для этого. вместо этого я создал зарезервированную таблицу, которую я назвал xtlist. это будет содержать список значений (1 столбец, если необходимо) для работы.</p><pre><code>create table [dbo].[xtlist]( [list] [varchar](1000) null ) on [primary] </code></pre><p>затем хранимая процедура для заполнения списка. это не является строго необходимым, но я думаю, что это очень полезно и наилучшей практики.</p><pre><code>-- ============================================= -- author: zark khullah -- create date: 20/06/2014 -- ============================================= create procedure [dbo].[xpcreatelist] @listquery varchar(2000) as begin set nocount on; delete from xtlist insert into xtlist exec(@listquery) end </code></pre><p>теперь просто разберитесь со списком так, как хотите, используя xtlist. вы можете использовать в процедуре (для выполнения нескольких команд t-sql) скалярные функции (для извлечения нескольких строк) или табличные функции с несколькими операторами (извлекает строки, но, как это было в таблице, 1 строка на строку). для этого вам понадобятся курсоры:</p><pre><code>declare @item varchar(100) declare clist cursor dynamic for (select * from xtlist where list is not null) open clist fetch first from clist into @item while @@fetch\_status = 0 begin << desired action with values >> fetch next from clist into @item end close clist deallocate clist </code></pre><p>желаемое действие будет следующим, в зависимости от того, какой тип объекта создан:</p><p><strong> хранимые процедуры </strong></p><pre><code>-- ============================================= -- author: zark khullah -- create date: 20/06/2014 -- ============================================= create procedure [dbo].[xpprocreateexec] ( @cmd varchar(8000), @replacewith varchar(1000) ) as begin declare @query varchar(8000) << cursor start >> set @query = replace(@cmd,@replacewith,@item) exec(@query) << cursor end >> end /* examples (list a,b,c) query = 'select x from table' with exec xpprocreateexec(query,'x') turns into select a from table select b from table select c from table cmd = 'exec procedure ''arg''' --whatchout for wrong quotes, since it executes as dynamic sql with exec xpprocreateexec(cmd,'arg') turns into exec procedure 'a' exec procedure 'b' exec procedure 'c' */ </code></pre><p><strong> скалярные функции </strong></p><pre><code>-- ============================================= -- author: zark khullah -- create date: 20/06/2014 -- ============================================= create function [dbo].[xfprocreatestr] ( @originaltext varchar(8000), @replacewith varchar(1000) ) returns varchar(8000) as begin declare @result varchar(8000) set @result = '' << cursor start >> set @result = @result + replace(@originaltext,@replacewith,@item) + char(13) + char(10) << cursor end >> return @result end /* example (list a,b,c) text = 'access provided for user x' with "select dbo.xfprocreatestr(text,'x')" turns into 'access provided for user a access provided for user b access provided for user c' */ </code></pre><p><strong> многозначные табличные функции </strong></p><pre><code>-- ============================================= -- author: zark khullah -- create date: 20/06/2014 -- ============================================= create function [dbo].[xfprocreateinrows] ( @originaltext varchar(8000), @replacewith varchar(1000) ) returns @texts table ( text varchar(2000) ) as begin << cursor start >> insert into @texts values(replace(@originaltext,@replacewith,@item)) << cursor end >> end /* example (list a,b,c) text = 'access provided for user x' with "select * from dbo.xfprocreateinrow(text,'x')" returns rows 'access provided for user a' 'access provided for user b' 'access provided for user c' */ </code></pre>

<p>следующее позволит вам быстро удалить дубликаты, нулевые значения и вернуть только действительный список.</p><pre><code>create table duplicatetable (col1 int) insert into duplicatetable select 8 union all select 1--duplicate union all select 2 --duplicate union all select 1 union all select 3 union all select 4 union all select 5 union select null go with cte (col1,duplicatecount) as ( select col1, row\_number() over(partition by col1 order by col1) as duplicatecount from duplicatetable where (col1 is not null) ) select col1 from cte where duplicatecount =1 go </code></pre><p>cte действительны в sql 2005, затем вы можете сохранить значения во временной таблице и использовать их со своей функцией.</p>

<h1>таблица передачи как параметр хранилище</h1><p><strong> шаг 1: </strong></p><p>create table [dbo] .t\_employees\_details ( id int, имя nvarchar (50), пол nvarchar (10), зарплата int )</p><p><strong> шаг 2: </strong></p><p>создать тип empinserttype as table (... id int, имя nvarchar (50), пол nvarchar (10), зарплата int )</p><p><strong> шаг 3: </strong></p><p>/ * необходимо добавить ключевое слово readonly в конец переменной * /</p><p>create proc prc\_empinserttype @ employeeinserttype empinserttype readonly as begin insert into [dbo] .t\_employees\_details select * from @employeeinserttype end</p><p><strong> шаг 4: </strong></p><p>объявить @employeeinserttype empinserttype</p><p>insert into @employeeinserttype values (1, 'john', 'male', 50000) insert into @employeeinserttype values (2, 'praveen', 'male', 60000) insert into @employeeinserttype values (3, 'chitra', 'female', 45000) insert into @employeeinserttype values (4, 'mathy', 'female', 6600) insert into @employeeinserttype values (5, 'sam', 'male', 50000)</p><p>exec prc\_empinserttype @employeeinserttype</p><p>=======================================</p><p>select * from t\_employees\_details</p><p><strong>результат</strong></p><p>1 джон мале 50000</p><p>2 правин мужской 60000</p><p>3 читра женский 45000</p><p>4 mathy женский 6600</p><p>5 sam male 50000</p>

<pre><code> create table project (projectid int, description varchar(50)); insert into project values (1, 'chase tail, change directions'); insert into project values (2, 'ping-pong ball in clothes dryer'); create table projectresource (projectid int, resourceid int, name varchar(15)); insert into projectresource values (1, 1, 'adam'); insert into projectresource values (1, 2, 'kerry'); insert into projectresource values (1, 3, 'tom'); insert into projectresource values (2, 4, 'david'); insert into projectresource values (2, 5, 'jeff'); select *, (select name + ' ' as [text()] from projectresource pr where pr.projectid = p.projectid for xml path ('')) as resourcelist from project p -- projectid description resourcelist -- 1 chase tail, change directions adam kerry tom -- 2 ping-pong ball in clothes dryer david jeff </code></pre>

<p>чтобы получить количество столбцов в таблице, используйте это:</p><pre><code>select count(id) from syscolumns where id = object\_id('tablename') </code></pre><p>и чтобы передать таблицу функции, попробуйте xml как show <a href="http://www.sqlservercentral.com/forums/topic484780-145-1.aspx">здесь</a> :</p><pre><code>create function dbo.readxml (@xmlmatrix xml) returns table as return ( select t.value('./@salary', 'integer') as salary, t.value('./@age', 'integer') as age from @xmlmatrix.nodes('//row') x(t) ) go declare @source table ( salary integer, age tinyint ) insert into @source select 10000, 25 union all select 15000, 27 union all select 12000, 18 union all select 15000, 36 union all select 16000, 57 union all select 17000, 44 union all select 18000, 32 union all select 19000, 56 union all select 25000, 34 union all select 7500, 29 --select * from @source declare @functionargument xml select @functionargument = ( select salary as [row/@salary], age as [row/@age] from @source for xml path('') ) --select @functionargument as [@functionargument] select * from readxml(@functionargument) /* -------- sample output: -------- salary age ----------- ----------- 10000 25 15000 27 12000 18 15000 36 16000 57 17000 44 18000 32 19000 56 25000 34 7500 29 */ </code></pre>