Как быть с динамическим sql в теле функции?

Я использую динамический sql в теле функции (это данность или условие задачи).

Функция возвращает таблицу. Содержимое таблицы я формирую в динамическом sql предложении. Если использовать временную таблицу #ttt то динамически я заполняю ее содержимое, но тогда я не могу ее использовать в контексте функции, если я декларирую таблицу @tt в контексте функции, то не могу передать ее в динамический контент sql.

Как быть?

Forums: 

Изображение пользователя st.

Похоже что таким образом никак

Похоже что таким образом никак.
Проверяется даже контекст динамического SQL, т.е. даже нельзя объявлять временную таблицу вне функции, потом ее вызывать.

CREATE TABLE #T(id int, name varchar(128))
GO
 
CREATE FUNCTION TableTest()
RETURNS @MyTable TABLE (id int, name varchar(128))
AS
BEGIN
	DECLARE @stm nvarchar(1000)
	SET @stm = 'INSERT INTO #T SELECT 1, ''Name1'''
	EXEC sp_executesql @stm
	SET @stm = 'INSERT INTO @MyTable SELECT * FROM #T'
	EXEC sp_executesql @stm
	RETURN
END
GO
 
SELECT * FROM TableTest()
GO
 
DROP TABLE #T
GO
DROP FUNCTION TableTest
GO

Msg 557, Level 16, State 2, Line 2
Only functions and extended stored procedures can be executed from within a function.

Изображение пользователя ipanshin.

Один путь я нашел.Через по

Один путь я нашел.

Через постоянную таблицу xx, куда я записываю строки в рамках динамического sql в теле функции с уникальным @id=newid() в том же контексте динамического sql. Потом этот @id я передаю в функцию и уже заполняю возвращаемую таблицу функции обычным

insert into @rettab
select * from xx where id=@id

Есть ли еще предложения?

Изображение пользователя st.

Видимо, лучше не по @id = newi

Видимо, лучше не по @id = newid(), а по @@spid.
Передать в функцию переменную типа table тоже нельзя...

А каким образом ты вызываешь динамический SQL в теле функции, это же запрещено?

Изображение пользователя ipanshin.

Похоже, что я ошибся. Прошла т

Похоже, что я ошибся. Прошла только компиляция. Пойду еще поищу решение на заднем дворе. К сожалению мне эту задачу надо как то решить. :(

Изображение пользователя ipanshin.

ДаСуществует функция типаC

Да
Существует функция типа
CREATE FUNCTION [dbo].[dvfn_row_get_child_ids_{FE27631D-EEEA-4E2E-A04C-D4351282FB55}],
в которой есть курсор

WHILE @@FETCH_STATUS = 0 BEGIN
--
INSERT INTO @ReturnTable ([RowID], [SDID])
SELECT tData.[RowID] , tData.[SDID]
FROM [dbo].[dvfn_row_get_child_ids_{EB1D77DD-45BD-4A5E-82A7-A0E3B1EB1D74}](@FetchedRowID, 0, 1) AS tData

Надо избавиться от курсора. Переменная курсора является параметром функции. Последовательность вызовов функций из функции также не определена. Вызовы происходят до тех пор пока @@ROWCOUNT>0
Понятно, что в таком динамическом контенте налетаем на вложенные курсоры, от которых я хочу избавиться.

Изображение пользователя st.

Не понял, а курсор внутри функ

Не понял, а курсор внутри функции объявляется что-ли?
Тогда можно его похерить и делать простой insert select from из того, что в курсоре.
Приведи код поподробнее, а то непонятно. Судя по жутким сигнатурам, функции автогенерируемые, поэтому вряд ли секретные :)

Изображение пользователя ipanshin.

Да код автогенерируем. Но дело

Да код автогенерируем. Но дело в том, что тело курсора состоит из вызовов тех же функций и переменная курсора является параметром , а количество вызовов определяется перед курсором.

IF NOT OBJECT_ID('[dbo].[dvfn_row_get_child_ids_{F94300EB-284E-4AB4-88AD-1E1D34D88F70}]') IS NULL
DROP FUNCTION [dbo].[dvfn_row_get_child_ids_{F94300EB-284E-4AB4-88AD-1E1D34D88F70}]

--Delimiter
GO

CREATE FUNCTION [dbo].[dvfn_row_get_child_ids_{F94300EB-284E-4AB4-88AD-1E1D34D88F70}] (
@RowID AS uniqueidentifier,
@ParentInTree AS bit,
@IncludeChildSections AS bit
) RETURNS @ReturnTable TABLE (RowID uniqueidentifier PRIMARY KEY )
AS
BEGIN
DECLARE @TreeTable TABLE (RowID uniqueidentifier PRIMARY KEY , Level int)

DECLARE @Level AS INT
SELECT @Level = 0

IF @ParentInTree = 1
BEGIN
INSERT INTO @TreeTable ([RowID], [Level])
SELECT [RowID] , 1
FROM [dbo].[dvtable_{F94300EB-284E-4AB4-88AD-1E1D34D88F70}] AS tData WITH(ROWLOCK)
WHERE ([RowID] = @RowID)

END ELSE
BEGIN
INSERT INTO @TreeTable ([RowID], [Level])
SELECT [RowID] , 1
FROM [dbo].[dvtable_{F94300EB-284E-4AB4-88AD-1E1D34D88F70}] AS tData WITH(ROWLOCK)
WHERE [ParentRowID] = @RowID
END

IF @IncludeChildSections = 1 BEGIN
DECLARE Row_Cursor CURSOR FAST_FORWARD FOR
SELECT [RowID]
FROM @TreeTable

DECLARE @FetchedRowID AS uniqueidentifier

OPEN Row_Cursor
FETCH NEXT FROM Row_Cursor
INTO @FetchedRowID

WHILE @@FETCH_STATUS = 0 BEGIN
--

INSERT INTO @ReturnTable ([RowID])
SELECT tData.[RowID]
FROM [dbo].[dvfn_row_get_child_ids_{39E04BFC-4FCC-421C-ABA2-84173090175E}](@FetchedRowID, 0, 1) AS tData

INSERT INTO @ReturnTable ([RowID])
SELECT tData.[RowID]
FROM [dbo].[dvfn_row_get_child_ids_{F73D85EC-89BF-4730-849A-10B4FEF8FE2C}](@FetchedRowID, 0, 1) AS tData

INSERT INTO @ReturnTable ([RowID])
SELECT tData.[RowID]
FROM [dbo].[dvfn_row_get_child_ids_{C2045B41-E6BB-4576-9AC5-32A953BCE9D2}](@FetchedRowID, 0, 1) AS tData

INSERT INTO @ReturnTable ([RowID])
SELECT tData.[RowID]
FROM [dbo].[dvfn_row_get_child_ids_{9536EE32-5188-4A82-8E58-B935DF7D2225}](@FetchedRowID, 0, 1) AS tData

FETCH NEXT FROM Row_Cursor
INTO @FetchedRowID
END

CLOSE Row_Cursor
DEALLOCATE Row_Cursor
END

INSERT INTO @ReturnTable ([RowID])
SELECT tData.[RowID]
FROM @TreeTable AS tData

RETURN
END

--Delimiter
GO

Изображение пользователя ipanshin.

Блин, похоже надо идти по пути

Блин, похоже надо идти по пути создания внешней функции
типа
CREATE ASSEMBLY [qq]
FROM 'function.dll'
GO

CREATE FUNCTION [dbo].[yy] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [qq].[class.SqlServer.qq].[function];
GO

Внешняя функция из функции вызывается без проблем.
Остается определиться можно ли из такой dll выполнять динамический код в БД (?)

Изображение пользователя st.

Черт ногу сломит в таком коде,

Черт ногу сломит в таком коде, даже автогенеренном. Проектировщику двойку ставить надо жирную.
Идея в общем понятна - дерево раскручивается.
Нужно убрать нахрен все эти курсоры и вызовы функций и воспользоваться рекурсивными запросами.
См. мои примеры здесь
http://www.arbinada.com/modules.php?name... в первой части.

Внешная функция не поможет, там тоже ограничения и даже побольше.

Изображение пользователя ipanshin.

Черт ногу сломит</

Черт ногу сломит

Ну да до основания разрушим, а затем мы наш, мы новый мир построим...
Если бы я так поступал, то я до сих пор работал вместо биллинга в петросвязи. Причем считал бы звонки вручную. А кто в это время будет крутить бизнесс процессы? Я тебе показал только один перл кода. Есть еще.
А совместимость версий, которые уже in productn?

Внешная функция не поможет

Это же простая dll? Из которой я могу сделать новую коннекцию к серверу и выполнить строку динамического кода, которую я передам в эту dll. Разве не так?

Изображение пользователя st.

Да понятно, что так трогать не

Да понятно, что так трогать нельзя... Надо сначала хотя бы тесты автоматические написать, а потом менять.
В CLR-функцию передается контекст текущего соединения, но в принципе можно открыть и обычное соединение.
В MSDN есть невнятная статья на эту тему, об ограничениях сказано мало, видимо, надо пробовать, но у меня есть сомнения по этому поводу, слишком уж большая дыра в безопасности образуется в этом случае.
http://msdn2.microsoft.com/en-us/library...

Изображение пользователя ipanshin.

Забодали своей безопасностью.

Забодали своей безопасностью. Скоро от этих condoms умрем.

Я получил

Msg 6265, Level 16, State 1, Line 3
CREATE ASSEMBLY failed because type "dll.exec.SqlServer.Wrap" in safe
assembly "exec_sql" has a pinvokeimpl method. P/Invoke is not allowed
in safe assemblies.

Представляете, если бы Бог предусмотрел "безопасность" языка при разговоре? Стихи писать лучше.

58

Избави Бог меня от рабского труда
Жить в твоем ритме смены развлечений,
Подсчитывать, о Жизнь, твои часы, когда
Искришься ты калейдоскопом увлечений.

В терпении труда, знаков вниманья ожидая,
Всем твоим прихотям свободу жертвой принося
Как будто Жизнь есть Смерть, а не кусочек Рая,
И бесконечного страдания тюрьма, но не в обиде я…

В желаньях Жизни привилегии велики,
Захочешь и свободно отдаешься ты назло
Всем проживающим рабам твоим безликим,
Прощая все, что здесь неправедно произошло.

О Жизнь, жизнь в ожиданьи и страданьи просто Ад
И иллюзорен развлечений твой искрящийся наряд.

Изображение пользователя ipanshin.

Еще получил. Это уже при откры

Еще получил. Это уже при открытии коннекции
string connectionString =
// "Persist Security Info=False;Data Source=panshini\s2005;Initial Catalog=master;User ID=sa;Password=Passw0rd;";

"Data Source=(local);Initial Catalog=test;Integrated Security=SSPI;User ID=sa;Password=Passw0rd;";

хотя вроде указал все верно(?)

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'sql':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at dll.exec.SqlServer.exec_sql.sql(String value)

Подумалось:если задача не им

Подумалось:
если задача не имеет приемлемого решения, может ее надо переформулировать.

Изображение пользователя ipanshin.

Исследования показали следующе

Исследования показали следующее:
При переходе в dll (create assembly) безопасность контента остается без изменения. Т.е. оформляя контент хранимой процедурой я спокойно меняю объекты БД, при оформлении контента функцией - я не могу менять никакие объекты БД. Использование сборок забавно: переходишь на с шарп и далее пишешь для сервера, как будто разрабатываешь клиента.

В поставленной задаче перехожу от функции к хранимой процедуре с применением динамического контента. Результат выложу.

Изображение пользователя ipanshin.

Блин, sql это тоже как стихи.

Блин, sql это тоже как стихи.
:)
Прошу посмотреть мою реализацию функции в виде процедуры с динамическим sql. Ну и жду критики. Только не надо забывать, что моя задача избавиться от курсоров в работающей системе, а не написать что либо истинно правильное с чистого листа.

CREATE PROCEDURE [dbo].[dvfn_row_get_child_ids_opt_{FE27631D-EEEA-4E2E-A04C-D4351282FB55}] (
@RowID AS uniqueidentifier,
@ParentInTree AS bit,
@IncludeChildSections AS bit,
@tbl varchar(256)
)
--RETURNS @ReturnTable TABLE (RowID uniqueidentifier PRIMARY KEY , SDID uniqueidentifier)
AS
BEGIN
----------
-- IP:GROP
----------
DECLARE @sql nvarchar(4000), @rtn INT, @cs INT, @Options INT,
@m int, @i int, @j int
DECLARE @Level AS INT, @callID uniqueidentifier

DECLARE @TreeTable TABLE (RowID uniqueidentifier PRIMARY KEY , SDID uniqueidentifier,
Level int, num int identity)

DECLARE @TrTbl_code TABLE ( sql nvarchar(512) DEFAULT ' ', num int identity )
DECLARE @exec_code TABLE ( sql nvarchar(512) DEFAULT ' ', num int identity )

SET @Level = 0
SET @callID = newID()

IF @ParentInTree = 1
BEGIN
INSERT INTO @TreeTable ([RowID], [SDID], [Level])
SELECT [RowID] , tData.[SDID], 1
FROM [dbo].[dvtable_{FE27631D-EEEA-4E2E-A04C-D4351282FB55}] AS tData WITH(ROWLOCK)
WHERE ([RowID] = @RowID)

WHILE(@@ROWCOUNT > 0) BEGIN
SELECT @Level = @Level + 1

INSERT @TreeTable ([RowID], [SDID], [Level])
SELECT tData.[RowID] , tData.[SDID], @Level + 1
FROM [dbo].[dvtable_{FE27631D-EEEA-4E2E-A04C-D4351282FB55}] AS tData WITH(ROWLOCK)
JOIN @TreeTable AS tNodes
ON tData.[ParentTreeRowID] = tNodes.[RowID]
WHERE tNodes.[Level] = @Level
END

END ELSE
BEGIN
INSERT INTO @TreeTable ([RowID], [SDID], [Level])
SELECT [RowID] , tData.[SDID], 1
FROM [dbo].[dvtable_{FE27631D-EEEA-4E2E-A04C-D4351282FB55}] AS tData WITH(ROWLOCK)
WHERE [ParentRowID] = @RowID
END

IF ( SELECT COUNT(*) FROM @TreeTable ) > 0
BEGIN

IF @IncludeChildSections = 1 BEGIN

-- GET_ROW_CHILD_IDS_CURSOR

-- #tmp__EB1D77DD_45BD_4A5E_82A7_A0E3B1EB1D74__1
INSERT INTO @TrTbl_code( sql ) SELECT
' EXEC [dbo].[dvfn_row_get_child_ids_opt_{EB1D77DD-45BD-4A5E-82A7-A0E3B1EB1D74}] tmplt, 0, 1, ''#child'' '

-- #tmp__5B7091C7_18DA_4E82_9C62_883F5237EED2__2
INSERT INTO @TrTbl_code( sql ) SELECT
' EXEC [dbo].[dvfn_row_get_child_ids_opt_{5B7091C7-18DA-4E82-9C62-883F5237EED2}] tmplt, 0, 1, ''#child'' '

-- #tmp__7B2E8093_A960_44C1_8F02_5F8B381B5398__3
INSERT INTO @TrTbl_code( sql ) SELECT
' EXEC [dbo].[dvfn_row_get_child_ids_opt_{7B2E8093-A960-44C1-8F02-5F8B381B5398}] tmplt, 0, 1, ''#child'' '

-- #tmp__F52F4439_30A9_4C03_BC93_94FD8DD6183B__4
INSERT INTO @TrTbl_code( sql ) SELECT
' EXEC [dbo].[dvfn_row_get_child_ids_opt_{F52F4439-30A9-4C03-BC93-94FD8DD6183B}] tmplt, 0, 1, ''#child'' '

-->
INSERT INTO @exec_code(sql)
SELECT replace(b.sql,'tmplt',''''+CONVERT(CHAR(36),a.RowID)+'''')
FROM @TreeTable a CROSS JOIN @TrTbl_code b
ORDER BY b.num, a.num

SET @m=( SELECT COUNT(*) FROM @exec_code)
SELECT @sql=' CREATE TABLE #child([RowID] uniqueidentifier, SDID uniqueidentifier, num int identity) '
IF @m<30 BEGIN
SELECT @sql = @sql + sql from @exec_code
SELECT @sql = @sql+ ' INSERT INTO [dbo].[dvgrp_fncall]' +
' ([callID], [RowID], [SDID] ) ' +
' SELECT @callID, [RowID], [SDID] FROM #child ORDER BY num '
EXEC sp_executesql @sql, N'@callID uniqueidentifier', @callID
END ELSE BEGIN

SET @i=1
WHILE ( @i <= @m ) BEGIN
SET @j=0
SELECT @sql=' CREATE TABLE #child([RowID] uniqueidentifier, SDID uniqueidentifier, num int identity) '
WHILE ( @j<30 AND @i<=@m ) BEGIN
SELECT @sql = @sql + sql from @exec_code WHERE num=@i
SELECT @j=@j+1
SELECT @i=@i+1
END
SELECT @sql = @sql+ ' INSERT INTO [dbo].[dvgrp_fncall]' +
' ([callID], [RowID], [SDID] ) ' +
' SELECT @callID, [RowID], [SDID] FROM #child ORDER BY num '

EXEC sp_executesql @sql, N'@callID uniqueidentifier', @callID
END
END
END

INSERT INTO [dbo].[dvgrp_fncall]([callID], [RowID], [SDID])
SELECT @callID,[RowID], [SDID] FROM @TreeTable ORDER BY num

SET @sql = ' INSERT INTO ' + @tbl + ' ([RowID], [SDID]) ' +
' SELECT tData.[RowID] , tData.[SDID] ' +
' FROM [dbo].[dvgrp_fncall] (NOLOCK) AS tData ' +
' WHERE tData.callID=@callID '
EXEC sp_executesql @sql, N'@callID uniqueidentifier', @callID

-- clearing
DELETE FROM [dbo].[dvgrp_fncall] WHERE callID=@callID

END
RETURN
END