November 16, 2003 at 3:16 pm
Can I call a user-defined function or stored procedure from a SQL statement in SQL server 2000?
For Example: If I have a function OBTAIN_ID which return an ID from table TABLE_ID
Can I utilize it via the SQL statement
SELECT OBTAIN_ID ...
Many thanks in advance.
Sincerely yours,
Hung Hoang
November 16, 2003 at 10:05 pm
Select dbo.OBTAIN_ID
or
Select * From dbo.OBTAIN_ID
if it returns a table.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
November 16, 2003 at 10:14 pm
Crispin,
Thanks for the info., I greatly appreciate it. Since I can not permanently modify a table in a user defined function, I have to create a stored procedure and then call the stored procedure from a function. However, when I ran the function I have the follwong error:
"Server: Msg 557, Level 16, State 2, Procedure fn_ObjectID, Line 7
Only functions and extended stored procedures can be executed from within a function."
Does SQL Server 2000 allow stored procedure to be called within a user defined function ?
Many thanks in advances.
Sincerely yours
Hung Hoang
November 16, 2003 at 10:35 pm
I sort of misread your post.
No, procs cannot be called from within a function.
Yes, functions can call functions.
Functions can call extended procs.
To get this to work, either copy the code from the proc and use it in the function or convert the proc to a function.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
November 17, 2003 at 9:56 am
Crispin,
The problem I have is that the stored procedure did modify a database table by insert a row into it. If I convert the stored procedure to function, the function will not allow the modification of a database table within the function. is there any trick to work around this ? Many thanks.
Have a great day!
Sincerely yours,
Hung Hoang
November 17, 2003 at 10:39 am
Not that I am aware of.
I am assuming you are calling the function from a proc.
Could you not insert the row into the table from within the proc, use the @@Identity and pass that value into the function.
From within there you can now use the data inserted into the row (if need be).
This is not the most eligant way of doing it. I would look at what you trying to accomplish and maybe come up with a better way.
Why actually use the function? Why not get the data you are after in the main proc?
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply