sp_executesql within a function 1

[Bygbobbo]

Anyone have a work around for using sp_executesql like in the situation below within a user defined function.

EXEC sp_executesql @rule,N'@check_rule bit OUTPUT',@check_rule OUTPUT

I have tested the function parts in query analyzer the results are what I want but when I create the function I get the following error.

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

When I remove the EXEC sp_executesql the function is fine, but I need to dynamically execute and return a value.

Thanks in advance,
bygs

 

[nigelrivett]

Can't be done. Functions are very restrictive in what they allow.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Put the resultset you want to work on into a temp table - you can then call an sp with can use dynamic sql to update the temp table.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.