July 11, 2002 at 3:16 am
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!!!
July 11, 2002 at 3:43 am
Are you just passing variables into MyFunction and not column values? If so then create a new SP instead of MyFunction e.g.:-
declare @i1 int
declare @i2 int
exec sp_MyFunction @intA, @intB, @i1 output
exec sp_MyFunction @intB, @intA, @i2 output
select @i1 = @i1 * 2, @i2 = @i2 * 3
update MyTable
set MyColA =
case
when MyColB = 1 then @i1
when MyColB = 4 then @i2
end
The new stored procedure sp_MyFunction can call the internal stored procedure you mention.
Regards,
Andy Jones
Edited by - andyj93 on 07/11/2002 03:43:17 AM
.
July 11, 2002 at 5:01 am
Unfortunately, my example was not so well-defined. Actually, MyFunction receives column values as parameters.
update MyTable
set MyColA =
case
when MyColB = 1 then dbo.MyFunction(MyColC, MyColD) * 2
when MyColB = 4 then dbo.MyFunction(MyColX, MyColY) * 3
end
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply