Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Red face Unanswered: using sp_execute within a user defined function

    Hi Folks,

    I try to use the extended procedure sp_execute in a user defined function which returns a table. The procedure is stored as extended procedure but I always get the Message:
    557 Only functions and extended stored procedures can be used in functions.

    Anybody a solution ??

    Thanx

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Can you post your code for the function?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Sep 2002
    Posts
    2

    Smile OK here is the code

    CREATE FUNCTION GetBalanceX(@EvaluationID int,@xDate nvarchar(8))
    RETURNS @AccountBalances TABLE (AccountNo nvarchar(10),Balance decimal(9),BalanceType char(1),AssetsLiabilitiesFlag char(1))
    BEGIN
    DECLARE @returnLogic nvarchar(250)
    DECLARE @readFromATable nvarchar(250) --this part would be read from a table

    -- @H and @S are declared and initialized in the original code
    -- I reduced it to the essential part
    SET @readFromATable = 'CASE WHEN @H-@S >0 THEN (@H-@S) ELSE (@H-@S) END,
    CASE WHEN @H-@S >0 THEN ''S'' ELSE ''H'' END,
    CASE WHEN @H-@S >0 THEN @HgreaterSKz ELSE @HlesserSKz END'


    SET @returnLogic = 'INSERT @AccountBalances SELECT @Account ,'+ @readFromATable



    EXEC sp_execute @returnLogic
    RETURN
    END

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    This was a tuffie....

    Although you are executing an extended stored procedure ,sp_execute, sp_execute acts like a nondeterministic function and is not allowed. Once I figured out what was happening I tried a few things to execute a dynamic SQL statment within a function but never could find an answer.

    Has anyone else solved this one?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The procedure sp_execute is used with procedures sp_prepare and sp_unprepare. You generally see them being used via SQL Profiler as in the example below:
    Code:
    sp_prepare @P1 output, N'@P1 varchar(11)', N'
    select * from titleauthor where au_id = @P1', 1 
    select @P1
    
    sp_execute 1, '172-32-1176'
    
    sp_unprepare 1
    This happens when the system creates a temporay stored procedure to be reused. There is no help from Books Online, maybe its because Microsoft never intended developers to use it. Have you looked at sp_executesql ? But you may not be able to use it since it is not an extended procedure.
    I don't use functions, yet, but shouldn't your function return something? It appears to me that it inserts data. Couldn't this be done be a stored procedure or use of a trigger?
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •