Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-29 : 08:13:19
|
| Tobias Andersen writes "Dear SQLTeam,I have started to work with and love the concept of what SQL Server 2000 does for me compared to the Access based solutions.However as i have been reading through messages boards ect. I have come to realize that the same thing can be achieved in many ways. While this is besides the point it leads to the question at hand.Im trying to incorporate some user-defined functions into my SQL Server DB. One of these is a generic function for calculating the "layer" of which a tree node resides in its SQL Server tree.However in order for me to do this for multiple SQL server trees i have had to author a Dynamic SQL Server statement that looks like this:SET @SQLCommand = N'SELECT @mylayer = COUNT(Tbl2.lft) FROM ' + @active_table_name + ' AS Tbl1, ' + @active_table_name + ' AS Tbl2 ' + 'WHERE Tbl1.lft BETWEEN Tbl2.lft AND Tbl2.rgt AND ' + 'Tbl1.' + @active_table_guid_column + ' = "' + CAST (@active_node_id As VARCHAR(100)) + '"'As you can see all it does is return an aggregate value to me into a variable "@mylayer", this is then executed with what I understand to be an extended stored procedure "sp_executesql"The line of code for that looks like so:EXEC sp_executesql @SQLCommand, N'@mylayer int OUTPUT', @mylayer = @layer OUTPUTThis stores the value of @mylayer into the @layer variable which is the return value for my user defined function.While the Enterprise manager seams to validate the syntax of the code. I seam to run into the following error message:"Only functions and extended stored procedures can be executed from within a function."Now if my logic is not totally wrong. I am indeed calling a extended stored procedure within my code "sp_executesql" and therefore the error message leaves me quite perplexed.Thank you,Tobias Andersen" |
|
|
jsmith
Starting Member
24 Posts |
Posted - 2003-05-29 : 11:14:42
|
| It's a feature of udf's - only extended stored procedures prefixed XP_ can be called. Extended stored procedures such as sp_OA..., or sp_ExecuteSQL cannot. See Ken Henderson - gurus guide to Stored Procedures, HTML and XML for more details & workarounds. |
 |
|
|
|
|
|
Extended store procedure error?