Передать таблицу как параметр в sql-сервер UDF
Я хотел бы передать таблицу как параметр в масштабирующее UDF.
Я также хотел бы ограничить параметр таблицами только одним столбцом. (Необязательно)
Возможно ли это?
ИЗМЕНИТЬ
Я не хочу передавать имя таблицы, я бы хотел передать таблицу данных (как полагаю я полагаю)
ИЗМЕНИТЬ
Я бы хотел, чтобы мой Scaler UDF в основном принимал таблицу значений и возвращал список строк в CSV.
IE
col1
"My First Value"
"My Second Value"
...
"My nth Value"
вернет
"My First Value, My Second Value,... My nth Value"
Я хотел бы сделать некоторую фильтрацию в таблице, хотя IE гарантирует, что нет нулей и чтобы не было дубликатов. Я ожидал чего-то вроде:
SELECT dbo.MyFunction(SELECT DISTINCT myDate FROM myTable WHERE myDate IS NOT NULL)
Ответы
Ответ 1
К сожалению, в SQL Server 2005 нет простого способа ответа. Ответ Lukasz верен для SQL Server 2008, хотя функция давно назрела
Любое решение будет включать временные таблицы или передачу в xml/CSV и разбор в UDF. Пример: изменить на xml, проанализировать в udf
DECLARE @psuedotable xml
SELECT
@psuedotable = ...
FROM
...
FOR XML ...
SELECT ... dbo.MyUDF (@psuedotable)
Что вы хотите сделать в картине большего размера? Возможно, есть еще один способ сделать это...
Изменить: почему бы не передать запрос в виде строки и использовать хранимый процесс с параметром вывода
Примечание: это непроверенный бит кода, и вам нужно подумать о SQL-инъекции и т.д. Однако он также удовлетворяет вашему требованию "один столбец" и должен помочь вам
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
Ответ 2
Вы можете, однако, никакой таблицы. Из документации:
Для функций Transact-SQL все данные типы, включая CLR, определенные пользователем типы и пользовательские типы таблиц, разрешены кроме данных временной метки тип.
Вы можете использовать пользовательские типы таблиц.
Пример пользовательского типа таблицы:
CREATE TYPE TableType
AS TABLE (LocationName VARCHAR(50))
GO
DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable
Итак, что вы можете сделать, это определить тип таблицы, например TableType
, и определить funcion, который принимает параметр этого типа. Пример функции:
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
Параметр должен быть READONLY. И пример использования:
DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable
SELECT dbo.Example(@myTable)
В зависимости от того, чего вы хотите достичь, вы можете изменить этот код.
EDIT:
Если у вас есть данные в таблице, вы можете создать переменную:
DECLARE @myTable TableType
И возьмите данные из таблицы в переменную
INSERT INTO @myTable(field_name)
SELECT field_name_2 FROm my_other_table
Ответ 3
В нижней строке вы хотите, чтобы запрос, например SELECT x FROM y, передавался в функцию, которая возвращает значения в виде строки, разделенной запятой.
Как уже было объяснено, вы можете сделать это, создав тип таблицы и передав UDT в эту функцию, но для этого требуется многострочный оператор.
Вы можете передавать XML без объявления типизированной таблицы, но для этого, похоже, нужна переменная xml, которая по-прежнему является многострочным оператором, т.е.
DECLARE @MyXML XML = (SELECT x FROM y FOR XML RAW);
SELECT Dbo.CreateCSV(@MyXml);
"FOR XML RAW" заставляет SQL дать вам набор результатов как некоторый xml.
Но вы можете обойти переменную с помощью Cast (... AS XML). Тогда это просто вопрос XQuery и небольшой конкатенационный трюк:
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));
До тех пор, пока вы используете FOR XML RAW, все, что вам нужно, это псевдоним столбца, который вы хотите использовать как значение, так как это жестко закодировано в функции.
Ответ 4
У меня была очень похожая проблема, и я смог добиться того, что искал, даже несмотря на то, что я использую SQL Server 2000. Я знаю, что это старый вопрос, но думаю, что он действителен для публикации здесь. решение, так как там должны быть другие, подобные мне, которые используют старые версии и по-прежнему нуждаются в помощи.
Вот трюк: SQL Server не будет принимать передачу таблицы в UDF, и вы не можете передать запрос T-SQL, чтобы функция создавала временную таблицу или даже вызывала хранимую процедуру для этого. Итак, вместо этого я создал зарезервированную таблицу, которую я назвал xtList. Это будет содержать список значений (1 столбец, если необходимо) для работы с.
CREATE TABLE [dbo].[xtList](
[List] [varchar](1000) NULL
) ON [PRIMARY]
Затем хранится процедура для заполнения списка. Это не является абсолютно необходимым, но я думаю, что это очень полезно и наилучшая практика.
-- =============================================
-- 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
Теперь просто обработайте список любым способом, используя xtList. Вы можете использовать в процедуре (для выполнения нескольких команд T-SQL), скалярных функций (для извлечения нескольких строк) или многозадачных табличных функций (извлекает строки, но, как и внутри таблицы, по 1 строке в строке). Для любого из них вам понадобятся курсоры:
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
Желаемое действие будет следующим: в зависимости от того, какой тип созданного объекта:
Сохраненные процедуры
-- =============================================
-- 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'
*/
Скалярные функции
-- =============================================
-- 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'
*/
Многозначные табличные функции
-- =============================================
-- 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'
*/
Ответ 5
Шаг 1: создайте тип как таблицу с именем TableType, который примет таблицу с одним столбцом varchar
create type TableType
as table ([value] varchar(100) null)
Шаг 2: создайте функцию, которая будет принимать выше объявленный TableType как параметр и значение строки в виде разделителя
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
Шаг 3. Передайте таблицу с одним столбцом varchar в пользовательский тип TableType и ',' в качестве разделителя в функции
select dbo.fn_get_string_with_delimeter(@tab, ',')
Ответ 6
Чтобы получить количество столбцов в таблице, используйте это:
select count(id) from syscolumns where id = object_id('tablename')
и передать таблицу функции, попробуйте XML как show здесь:
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
*/
Ответ 7
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
Ответ 8
Ниже вы можете быстро удалить дубликаты, нулевые значения и вернуть только действительный в качестве списка.
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
CTE действительны в SQL 2005, вы можете сохранить значения в таблице temp и использовать их с вашей функцией.
Ответ 9
ТАБЛИЦА ПРОПУСКА КАК ПАРАМЕТР В ЗАПУСКЕ ПРОЦЕДУРЫ
Шаг 1:
CREATE TABLE [DBO].T_EMPLOYEES_DETAILS
(
Id int,
Имя nvarchar (50),
Пол nvarchar (10),
Зарплата int
)
Шаг 2:
СОЗДАТЬ ТИП EmpInsertType AS TABLE
(
Id int,
Имя nvarchar (50),
Пол nvarchar (10),
Зарплата int
)
Шаг 3:
/* Должно добавить ключевое слово READONLY в конце переменной */
CREATE PROC PRC_EmpInsertType
@EmployeeInsertType EmpInsertType READONLY
В ВИДЕ
НАЧАТЬ INSERT INTO [DBO].T_EMPLOYEES_DETAILS SELECT * FROM @EmployeeInsertType
END
Шаг 4:
DECLARE @EmployeeInsertType EmpInsertType
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)
EXEC PRC_EmpInsertType @EmployeeInsertType
=======================================
SELECT * FROM T_EMPLOYEES_DETAILS
OUTPUT
1 John Male 50000
2 Мужчины Правэны 60000
3 Chitra Female 45000
4 Mathy Female 6600
5 Сам Мужчина 50000
Ответ 10
вы можете сделать что-то вроде этого
/* СОЗДАТЬ ТИП ТАБЛИЦЫ ОПРЕДЕЛЕННОГО ПОЛЬЗОВАТЕЛЯ */
CREATE TYPE StateMaster AS TABLE
(
StateCode VARCHAR(2),
StateDescp VARCHAR(250)
)
GO
/* CREATE FUNCTION, КОТОРЫЙ ПРИНИМАЕТ ТАБЛИЦУ КАК ПАРАМЕТР */
CREATE FUNCTION TableValuedParameterExample(@TmpTable StateMaster READONLY)
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE @StateDescp VARCHAR(250)
SELECT @StateDescp = StateDescp FROM @TmpTable
RETURN @StateDescp
END
GO
/* СОЗДАТЬ ЗАПОМНЕННУЮ ПРОЦЕДУРУ, КОТОРУЮ ПРИНИМАЕТ ТАБЛИЦУ КАК ПАРАМЕТР */
CREATE PROCEDURE TableValuedParameterExample_SP
(
@TmpTable StateMaster READONLY
)
AS
BEGIN
INSERT INTO StateMst
SELECT * FROM @TmpTable
END
GO
BEGIN
/* DECLARE VARIABLE OF TABLE USER DEFINED TYPE */
DECLARE @MyTable StateMaster
/* INSERT DATA INTO TABLE TYPE */
INSERT INTO @MyTable VALUES('11','AndhraPradesh')
INSERT INTO @MyTable VALUES('12','Assam')
/* EXECUTE STORED PROCEDURE */
EXEC TableValuedParameterExample_SP @MyTable
GO
Подробнее см. по ссылке: http://sailajareddy-technical.blogspot.in/2012/09/passing-table-valued-parameter-to.html