Tricks on how to execute string inside a function in Sql Server

Procedure FunctionX, Line 345

Invalid use of a side-effecting operator ‘EXECUTE STRING’ within a
function.

  • SQL run from Excel cannot use a temporary table
  • Inserting multiple rows in a single SQL query?
  • How do I transfer encrypted stored procedures from online DB to local?
  • SQL Server 2008 compare two tables in same database and get column is changed
  • How to Create Dynamic Columns in SSRS Report
  • how to set a specific time interval for different work shifts to retrieve data
  • I get the above error when I execute a dynamic statement inside a function in SQL Server 2012.
    Is there a workaround for this? Any tricks?

    PS: The sproc (stored procedure) is much too lengthy for its body to be inserted as-is inside the function.

    DECLARE @execsql NVARCHAR(2000)
    Set @execsql = 'INSERT INTO @TABLE1 EXEC SPROC1 ' + @ID_COMPANY + ',' + @ID_COUNTRY 
    exec (@execsql)
    

    Many thanks in advance.

    Also, I need to be able to delete inside the function as well. I know this contradicts the definition of functions but I am wondering if there are some tricks that can be used

    One Solution collect form web for “Tricks on how to execute string inside a function in Sql Server”

    No there is no tricks, see The Curse and Blessings of Dynamic SQL

    Dynamic SQL in User-Defined Functions

    This very simple: you cannot use dynamic SQL from used-defined
    functions written in T-SQL
    . This is because you are not permitted do
    anything in a UDF that could change the database state (as the UDF may
    be invoked as part of a query). Since you can do anything from dynamic
    SQL, including updates, it is obvious why dynamic SQL is not
    permitted.

    I’ve seen more than one post on the newsgroups where people have been
    banging their head against this. But if you want to use dynamic SQL in
    a UDF, back out and redo your design. You have hit a roadblock, and in
    SQL 2000 there is no way out.

    In SQL 2005 and later, you could implement your function as a CLR
    function. Recall that all data access from the CLR is dynamic SQL.
    (You are safe-guarded, so that if you perform an update operation from
    your function, you will get caught.) A word of warning though: data
    access from scalar UDFs can often give performance problems. If you
    say

    SELECT … FROM tbl WHERE dbo.MyUdf(somecol) = @value

    and MyUdf performs data access, you have more or less created a hidden
    cursor.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.