none
How to create a dynamic multi-line function in SQL Server

    Question

  • I am attempting to create a Multi-Line Function in SQL Server that accepts a dynamic WHERE clause as a parameter. I need this so that the function can be as versatile as possible for the filter that needs to be applied. I am unfortunately getting an error upon creation of the function.  I don't know how to solve the problem. Can someone advise me?

    SQL:


    SET

    ANSI_NULLSON

    GO

    SET

    QUOTED_IDENTIFIERON

    GO


    -- =============================================

    -- Author:

    -- Create date: 2/3/2014

    -- Description: This multiline function will accept a generic WHERE Clause and apply it to the query for return.

    -- =============================================


    CREATE

    FUNCTIONTESTMULTILINEFUNCTION


    (


    @WHEREvarchar(1024)


    ,@CHANGEDDATEasdatetime


    )


    RETURNS


    @TESTTABLE

    TABLE


    (


    IDint


    ,REVint


    )


    AS

    BEGIN


    Declare@SQLSTRINGvarchar(4096)


    SET@SQLSTRING=''


    SET@SQLSTRING=@SQLSTRING+'SELECT REVS.ID, REVS.Revision

    FROM

    (

    Select distinct result.ID, Max(Rev) as ''''Revision''''

    FROM

    (

    Select * from dbo.BugsAll

    where

    [Changed Date] < @CHANGEDDATE



    ) result



    GROUP BY result.ID

    ) REVS

    join dbo.BugsAll BA on (BA.ID=REVS.ID AND BA.REV=REVS.revision)'


    IF (@WHEREisnotnullOR@WHERE<>'')


    BEGIN


    SET@SQLSTRING=@SQLSTRING+' WHERE '+@WHERE;


    END


    INSERT@TESTTABLE

    EXEC (@SQLSTRING)


     

    RETURN

    END

    GO

    ERROR:

    Msg 443, Level 16, State 14, Procedure TESTMULTILINEFUNCTION, Line 44

    Invalid use of a side-effecting operator 'INSERT EXEC' within a function.


    Senior Test Lead -- Microsoft

    Monday, February 03, 2014 9:21 PM

Answers

  • No, this is not a feature gap. This is very much the design. A function must not change database state. It if were able, this is could have very interesting effects what result a query could return. Depending on where and how often the function is invoked in the query plan, the result could be very different.

    And, no, passing a WHERE clause as parameter is simply bad design. Either you have all your SQL in stored procedures or function, or you have them entirely in the client, but not in both places. Modules should be loosely coupled, not tightly. As for your management costs, it exactly this kind tight depedency that increases the maintenance costs, becuase the smallest change may mean that you need to change code all over the place.

    So it is back to the drawing board and devise a solution based on sound software-enginerring principles. And based on syntax that actually works.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 04, 2014 8:58 AM

All replies

  • We cannot use dynamic sql and insert-exec within user functions. There are some workaround this problem:

    1. using procedure instead
    2. using openquery
    3. CLR functions

    more info in this thread:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cc380898-7ae9-4601-87de-36ce4629477d/openquery-in-a-function


    sqldevelop.wordpress.com

    Monday, February 03, 2014 9:35 PM
  • Like said above, its not possible to use Dynamic sql or temp tables in a SQL Function.

    To overcome this, you can use a stored procedure instead. I have created one that does similar work as shown below.

    CREATE PROCEDURE usp_test
    (
    @Birthdate datetime
    )
    AS
    BEGIN
    
    Declare @SQLSTRING NVARCHAR(max), @WHERE varchar(max)
    
    SET @SQLSTRING='SELECT BusinessEntityID, NationalIDNumber 
    						   FROM HumanResources.Employee WHERE 1 = 1 ' 
    						   
    SET @WHERE = 'AND [Birthdate]>='''+ CONVERT(VARCHAR(10), @Birthdate, 120)+''''
    
    IF (@WHERE is not null OR @WHERE<>'')
    BEGIN
    SET @SQLSTRING = @SQLSTRING+@WHERE;
    END
    
    DECLARE @TABLE TABLE(BusinessEntityId INT, NationalIDNumber INT)
    
    INSERT INTO @TABLE
    EXECUTE sp_executesql @SQLSTRING
    
    SELECT * FROM @TABLE
    
    END
    GO

    Execute the procedure using the input parameter as below.

    EXEC usp_test '1970/01/01'

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Monday, February 03, 2014 10:00 PM
  • As other have said, you cannot use dynamic SQL in a function. Apart from the the whole concept of sending in a WHERE clause is plain wrong. It's OK to send in search parameters and build an SQL string from them. But forming an SQL Statement in different parts of your code? No way.

    If you want to do something like dynamic searches, see my article on dynamic search conditions on
    http://www.sommarskog.se/dyn-search-2008.html
    You can do this efficiently on modern versions of SQL Server without dynamic SQL.

    By the way, the web UI has a funny button with two arrows pointing from each other. You can use that button to insert code without the UI mangling the code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 03, 2014 11:14 PM
  • Thank you all for answering my question.  Seems like there is a feature gap in SQL Server 2012.

    Unfortunately I really need to from a dynamic query in a table valued function on the SQL SERVER. I have another tabled valued function that needs something returned as a table in order to further join the data. I am not allowed to use SPROCs in that function.

    Plus, there are occasions where I don't want to pass in a field parameter or need to change a parameter list such that I don't wish the table function to filter by a particular field or other setting. My application pushes the WHERE clause from EXCEL to SQL to do the hard work as EXCEL is not the application in which I want to process the SQL statement and pass it via ODBC. I cannot run macros in Excel on the web.

    I am bummed about the fact that this feature doesn't work. It will up my server management costs to maintain unique tabular based functions based on WHERE  clause query.


    Senior Test Lead -- Microsoft

    Tuesday, February 04, 2014 12:59 AM
  • >> Unfortunately I really need to form a dynamic query in a table valued function on the SQL SERVER. I have another tabled valued function that needs something returned as a table in order to further join the data. I am not allowed to use Stored Procedures in that function. <<

    You do know that real SQL programmers hate the proprietary nightmare of tabled valued functions?  This is how you procedural programmers avoid learning set-oriented declarative and functional programming. 

    Your mindset wants to write to a scratch tape or disk file (aka “tabled valued function result table”) just like you did BASIC, FORTRAN or COBOL. QL programmers do not have to materialize their data. We can use VIEW or a drive table as well as a base table. 

    >> Plus, there are occasions where I don't want to pass in a field [sic: columns are not fields] parameter or need to change a parameter list such that I don't wish the table function to filter by a particular field [sic] or other setting. <<

    What you want is a magical “Automobiles, Squids and Lady Gaga” function. An SQL programmer might write a complex VIEW then do simpler SELECTs off it. 

    >> My application pushes the WHERE clause from EXCEL to SQL to do the hard work as EXCEL is not the application in which I want to process the SQL statement and pass it via ODBC. I cannot run macros in Excel on the web.<< 

    This is a crazy language system. Usually we fetch data in SQL and then pass it to a math package, report writer, etc. We never keep logic (aka WHERE clauses) outside the database. 

    >> I am bummed about the fact that this feature doesn't work. It will up my server management costs to maintain unique tabular based functions based on WHERE clause query <<

    So stop writing those “tabular based functions”, change your mindset and start learning SQL and do it right. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, February 04, 2014 2:24 AM
  • No, this is not a feature gap. This is very much the design. A function must not change database state. It if were able, this is could have very interesting effects what result a query could return. Depending on where and how often the function is invoked in the query plan, the result could be very different.

    And, no, passing a WHERE clause as parameter is simply bad design. Either you have all your SQL in stored procedures or function, or you have them entirely in the client, but not in both places. Modules should be loosely coupled, not tightly. As for your management costs, it exactly this kind tight depedency that increases the maintenance costs, becuase the smallest change may mean that you need to change code all over the place.

    So it is back to the drawing board and devise a solution based on sound software-enginerring principles. And based on syntax that actually works.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 04, 2014 8:58 AM