Call for a sp within a user-defined function...

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Call for a sp within a user-defined function...

Author

Topic

ttcrissy
Starting Member

8 Posts

Posted - 2002-07-11 : 05:07:51

Hi

I have a huge update statement that calls for a user-defined function to set the values for one column. The update statement looks, in short, like this:

update MyTable
set MyColA =
case
when MyColB = 1 then dbo.MyFunction(@intA, @intB) * 2
when MyColB = 4 then dbo.MyFunction(@intB, @intA) * 3
end

My problem is that MyFunction should be able to call a stored procedure, which I know it's a bit imposible. The user-defined function can only call extended stored procedures. Then my solution would be to create an extended stored procedure that calls my stored procedure. This also means that I have to create a DLL to call for my stored procedure for me.

I'm asking you if it is worth trying to create a dll to call a stored procedure, or i'd better turn the whole update statement into a while loop, and call for a stored procedure instead of a user-defined function to update my values.

Thanks a lot!!!

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-11 : 10:32:16

I know this is a little bit out there... But I've seen it work.

Include this in your function

create table #ProcCall (col1 int)
go
create trigger Proc_Call on #ProcCall
for insert
as
execute storedProc1

Then to call your storedProc just insert into this table. If you have information you wish to pass over to storedProcedure, just put it into the temp table.

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources