CODEWITHSUNDEEP
sql-server
unknown (google)
unknown (google)

Reputation:

How to call a Stored procedure form a user defined function In SQL 2000

How to call a Stored procedure form a user defined function In SQL 2000

Upvotes: 0

Views: 439

Answers (2)

SqlRyan
SqlRyan

Reputation: 33944

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

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15685

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

Upvotes: 1

Related Questions