How to call a Stored procedure form a user defined function In SQL 2000
+1
A:
officially you can't.
but you can try this trick:
-- add 'loopback' linkedserver
if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
go
select * from openquery(loopback, 'exec yourSproc')
go
Mladen Prajdic
2009-07-01 20:21:31
That's about the sketchiest thing I've ever seen. I want to upvote you, not because I think that's a good idea, but because I respect your ingenuity...
rwmnau
2009-07-01 21:12:56
upvote away :)this method is actually used for things like that quite often and successfully.
Mladen Prajdic
2009-07-01 22:25:27
A:
According to this article, one of the limitations is that you can't call an SP from a UDF.
One of the features of a UDF is that they're deterministic - calling them repeatedly with the same input values will result in the same output values (also assuming the underlying data isn't changing). If you call other objects from a UDF, SQL Server can't guarantee that this will remain true - that the UDF will remain deterministic. For example, if you call a SP from your UDF, even if the SP is currently deterministic (doesn't contain any non-deterministic functions, like GETDATE), there's no guarantee that won't change.
For an explanation of what it means to be (non-)deterministic, check out wiki or MSDN
rwmnau
2009-07-01 20:30:32