none
Only functions and extended stored procedures can be executed from within a function.

    Question

  • Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
    DECLARE @sCellName VARCHAR(8)
    DECLARE @dDate DATETIME
    SET @sCellName = "CELL1M_1"
    SET @dDate = CAST('06/Jun/2006' AS DATETIME)

    EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate

    It work fine and return the desired result, but when l used this function in SQL,
    SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
      FROM GSM.GSMCellDaily_vw
     WHERE CSSR BETWEEN 0 AND 85
    AND FULLDATE = @dDate
    AND CM_SERV > 30
    AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)

    I got the following error, i.e.
    Msg 557, Level 16, State 2, Line 19
    Only functions and extended stored procedures can be executed from within a function.

    Does anyone have any idea on this, and what's the workaround for this?

    Thanks you!

    Wednesday, June 07, 2006 10:40 AM

Answers

All replies

  • By the way, l am using SQL Server 2005 Standard Edition
    Wednesday, June 07, 2006 10:46 AM
  • That is by design. No workarounds.
    Wednesday, June 07, 2006 12:18 PM
  • Actually...there is a workaround....move the T-SQL code in your stored procedure into a function and then call the function from your stored procedure. Then you can use your function in other places and your stored proc will still work as it does now.

    Friday, December 08, 2006 3:37 PM
  • Can any one please explain with eg more clearly how you have solved this problem.. 

    It would really be great.  

    Tuesday, December 04, 2007 8:37 AM
  • Hi,

    I am been running into the same error can some one please let me know what the resolution is?

    Thanks.

    Salman.

     

    Thursday, April 17, 2008 4:04 PM
  • I took your idea and copied my SP into the function but it says I can not use tempory tables in my function? How do I get around this?

     

    I want to call a stored proc which returns a table, I want to use the table in another stored proc using a join.

     

    So stored proc 1 does a heap of joins and uses a heap of temp tables and returns a table.

     

    I want to call this function in a query using a join to other tables. Since I can not select from a sp I thought I would use a function, oh function can not have temp tables, so I thought I would call my sp from a function and use my function call in the select with the joins, but no, you can not do this as you can not call an sp in a function.

     

    Why is it so hard to do the simplest things? because if it was easy anyone could do it.

     

    • Proposed as answer by John Prouty Tuesday, August 11, 2009 7:31 PM
    Thursday, November 06, 2008 3:33 AM
  • I have the same Issue, now Ihave made a stored proc that creates a new table and one that deletes the tables. I use the created table in the function but I get the same result.
    Wednesday, January 14, 2009 12:30 PM
  • This is not the kind of activity that should be done inside of a function.  Also, are you talking about a scalar function -- that is a function that returns a single value -- or are you talking about a function that returns a table?

    In either case, can you desribe more clearly exactly what you are trying to do?


    Kent Waldrop Ja09
    Wednesday, January 14, 2009 1:13 PM
    Moderator
  • You can't use temporary tables in functions, but you can use table variables. 
    Try:
    DECLARE @MyTableVariable TABLE(ID INT IDENTITY NOT NULL, SomeFieldName VARCHAR(50))

    instead of
    CRATE TABLE #MyTemporaryTable(ID INT IDENTITY NOT NULL, SomeFieldName VARCHAR(50))

    Hope this helps.
    Thursday, March 12, 2009 8:50 PM
  • Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
    DECLARE @sCellName VARCHAR(8)
    DECLARE @dDate DATETIME
    SET @sCellName = "CELL1M_1"
    SET @dDate = CAST('06/Jun/2006' AS DATETIME)

    EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate

    It work fine and return the desired result, but when l used this function in SQL,
    SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
      FROM GSM.GSMCellDaily_vw
     WHERE CSSR BETWEEN 0 AND 85
    AND FULLDATE = @dDate
    AND CM_SERV > 30
    AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)

    I got the following error, i.e.
    Msg 557, Level 16, State 2, Line 19
    Only functions and extended stored procedures can be executed from within a function.

    Does anyone have any idea on this, and what's the workaround for this?

    Thanks you!

    <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:70.85pt 3.0cm 70.85pt 3.0cm; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    Hello everybody,

    I recently had a similar issue. In fact the error message is not properly formatted since sp_executesql is an extended stored procedure as you can check by the following script:

    select objectproperty(object_id('sp_executesql'),'IsExtendedProc')
    returns
    -----------
    1

    Since we can’t use sp_executesql even it’s a XP, I had to found another workaround by using sp_OAMethod: My scenario was: how to find dynamically the number of rows in a table according some criteria (null values in my scenario). Using sp_OAMethod I built the following function:

    --------------------------------

    IF object_id(N'dbo.fc_ContaRegistros_x_Criterio') is not null DROP FUNCTION [dbo].[fc_ContaRegistros_x_Criterio]

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE FUNCTION dbo.fc_ContaRegistros_x_Criterio (

                @str_TBName VARCHAR(100),

                @str_Criter VARCHAR(500))

    RETURNS BIGINT

    AS

    BEGIN

    -- Objetivo   : Contar numero de registros de uma determinada tabela de acordo com o critério passado

    -- Criação    : Josué Monteiro Viana - 09/07/09

    /*

    Exemplo:

                DECLARE @count INT

                SET @count = dbo.fc_ContaRegistros_x_Criterio('master.dbo.sysobjects', '')

                PRINT @count

                SET @count = dbo.fc_ContaRegistros_x_Criterio('crk.dbo.acao', 'where cod_acao is null')

                PRINT @count

    */

                DECLARE

                            @int_objSQL INT,

                            @int_erros INT,

                            @int_objSelectCountResult INT,

                            @bint_SelectCount BIGINT,

                            @sql NVARCHAR(2000)

     

                EXEC @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT

                EXEC @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE

                EXEC @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'

                --SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' WHERE ' + @str_Criter

                SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' ' + @str_Criter

                SET @sql = 'ExecuteWithResults("' + @sql + '")'

                EXEC @int_erros = sp_OAMethod @int_objSQL, @sql, @int_objSelectCountResult OUTPUT

                EXEC @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT

                EXEC @int_erros = sp_OADestroy @int_objSQL

                -- debug info: not valid inside a fc

                --if @int_erros <> 0 EXEC sp_OAGetErrorInfo @int_objSQL else print 'ok'

                if @int_erros <> 0 SET @bint_SelectCount = @int_erros

                RETURN @bint_SelectCount

    END

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    --------------------------------

     

    I know your case is a little different, but I’m sure you can use this udf as a guideline to help you.

     

    Best wishes,

    Josue Monteiro Viana

     


    Josué Monteiro Viana
    Friday, July 10, 2009 3:11 PM
  • That is by design. No workarounds.

    I'd love to hear their excuse for this "design" choice. It absolutely ruins code management by forcing me to copy/paste source instead of just calling a proc from the UDF (which i only have to do because they haven't provided a suitable) "SELECT (EXEC someProcWithOneOutputParam....)" syntax to begin with.

    I'm sure they can come up with all sorts of arguments against how it COULD be used incorrectly and hurt performance, but considering the workarounds that people have to come up with, it seems like a mute point since those are generally bloated and horrible.


    I.T. Man of many talents, master of none. Android enthusiast and owner of http://AllDroid.org

    Wednesday, February 15, 2012 7:12 AM
  • really? who decided what kind of activities should be done in functions?!?

    ps

    it works with mysql, oracle and postgresql (even with sybase and db2)

    so... wrong server folks!

    Thursday, January 16, 2014 9:28 PM
  • Most likely, it is more about the difficulty of keeping the transactional behavior consistent and reliable, and to simply "make it work" without any side effects.

    Especially if we are talking about functions in Insert, Update and Delete statements, but of course functions that change persistent data pose the same difficulty with Select statements.

    In general, scalar UDFs have a negative impact on performance, even without any data retrieval in the UDF. Usually table valued UDFs don't have a big impact.

    You expect things from SQL for which SQL was never designed.

    Please note that Microsoft did not invent SQL, they are just one of the vendors that made a successful implementation of SQL in an RDBMS.


    Gert-Jan


    Thursday, January 16, 2014 10:33 PM