May 7, 2008 at 3:38 pm
I have the following code that executes a dynamic sql to populate a table variable (ignore the flowr) and return it. The idea is to use it with cross apply. The function compiles successfully but when I execute it fails with the error.
any ideas?
Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.
UDF code:
CREATE FUNCTION fnGetBoColumns
(
@in_txnidvarchar(50),
@in_bo_namevarchar(255)
)
RETURNS
@tblBoColumns TABLE
(
[tnxid]nvarchar(50),
[bo]nvarchar(255),
[column]nvarchar(1000)
)
AS
BEGIN
declare @params nvarchar(max)
set @params=N'@boName varchar(max), @txnid uniqueidentifier'
declare @flowr nvarchar(1000)
declare @sql nvarchar(max)
set @flowr=N'distinct-values(for $a in /data/'+@in_bo_name+'/row/* return concat(local-name($a),","))'
set @sql='insert into @tblBoColumns select txnid, '''+@in_bo_name+''' Bo, [Value] from (select txnid, statexml.query('''+@flowr+''').value(''.'',''nvarchar(max)'') columns from state_from_events ) as tmp cross apply fnStrToArr(tmp.columns, '','') where txnid='''+@in_txnid+''' and len([Value])>0 order by [Value]'
exec sp_executesql @sql
RETURN
END
May 7, 2008 at 4:22 pm
You cannot execute stored procedures from within UDFs, and sp_executesql is a stored procedure.
You have to find other way to do your task, it seems.
Piotr
...and your only reply is slàinte mhath
May 7, 2008 at 7:53 pm
at first glance, i don't think you need dynamic sql. try something like this:
CREATE FUNCTION fnGetBoColumns
(
@in_txnidvarchar(50),
@in_bo_namevarchar(255)
)
RETURNS
@tblBoColumns TABLE
(
[tnxid]nvarchar(50),
[bo]nvarchar(255),
[column]nvarchar(1000)
)
AS
BEGIN
declare @params nvarchar(max)
set @params=N'@boName varchar(max), @txnid uniqueidentifier'
declare @flowr nvarchar(1000)
declare @sql nvarchar(max)
insert into @tblBoColumns
select txnid, @in_bo_name as Bo, [Value]
from (
select txnid,
statexml.query(@flowr).value('.','nvarchar(max)') columns
from state_from_events ) as tmp
cross apply fnStrToArr(tmp.columns, ',')
where txnid= @in_txnid
and len([Value])>0
order by [Value]
RETURN
END
May 8, 2008 at 6:42 am
thanks. I've tried that as well, its a SQL 2005 XQuery limitation; accepts only string literals. Below is the error
The argument 1 of the xml data type method "query" must be a string literal.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply