April 12, 2004 at 9:32 am
Ok, I've been using functions a lot in SQL Server, I know how they work, what they can and cannot do. However, I've now come to the point where it would be really nice to be able to create a Temp Table, or even execute another Stored Procedure from a Function. Does anyone know if Yukon is supporting this?
April 13, 2004 at 8:26 am
Not sure what you mean, UDF's as they exist today within SQL 2000 allow for calling of stored procedures...are you trying to maintain some sort of intermidate results of say multiple sprocs ect wihthin the same temp table?
In that situation, I have usually modified the sprocs passing in an additional param and have them act accordingly on weather it should just return a results set or store the results within a session temp table. This method works/ed in 7.0...in 2000 you might be able to pass a table object into the sproc(s), though I believe there are restrictions on the table data type that might not allow that.
April 13, 2004 at 8:43 am
Ok, what I mean is this...
If I try to create a table, or even a Temp Table for that matter within a Function, it will not allow it.
Create Table
#File(INI_Text NVarChar(255))
Ceating a Table variable will work, however, you cannot insert into a Table variable executing a Stored Proc!
Insert Into
#INIFile (INI_Text) Exec master..xp_CmdShell @Cmd
Insert Into @Temp
(INI_Text) Exec master..xp_CmdShell @Cmd
So, in order to get around this, I created a Stored Proc to get the results from another Stored Proc (show above), but then, trying to execute that NEW Stored Proc from within a Function does NOT work! The Error message is something Like "You can only execute Extended Procedures or other Functions from within a Function"
So, there's the catch 22!
1.) Can't use an existing table and insert into it from a Function
2.) Can't create a Temp Table in a Function
3.) Can create a Table Variable, but can't Insert into it using results from a Stored Proc.
4.) Can't execute Stored Procs from a Function (unless they are extended).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply