none
User Defined Function to Stored Procedure call? RRS feed

  • Question

  • Hello,

     

    Can we call stored procedure from user defined function and vice-versa??

     

    Thanks in advance.

     

    Monday, April 7, 2008 6:29 AM

All replies

  • You can't call a stored procedure from a user-defined function, but you can call a user-defined function from a stored procedure.

     

    Iain

     

    Monday, April 7, 2008 6:33 AM
  • Hi,

    there is some workaround (if you don't use parameters):

     

    create procedure dbo.stp_call_from_udf

    as

    select 1 as c

    go

    create function dbo.fn_call_stp ()

    returns int

    as

    begin

    returns int

    as

    begin

    return (

    SELECT a.*

    FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',

    'exec forum.dbo.stp_call_from_udf') AS a)

    end

    go

    select dbo.fn_call_stp ()

    Monday, April 7, 2008 7:10 AM
  • Denis,

    A small correction in your code. The function has the statement 'returns int' twice.

     

    I would slightly modify it as:

     

    Code Snippet

    create function dbo.fn_call_stp ()

    returns int

    as

    begin

    return (

    SELECT top (1) c

    FROM OPENROWSET('SQLNCLI', 'Server=servername;Trusted_Connection=yes;',

    'execute dbname.dbo.stp_call_from_udf') AS a)

    end

     

     

     

     

    Monday, April 7, 2008 8:17 AM
    Moderator
  • OpenRowSet() works only if you don't use parameters, or there seems no way to exec a sproc in UDF.

     

    Monday, April 7, 2008 10:04 AM