locked
Rewriting scalar function to table function RRS feed

  • Question

  • Hi,

    I have just re-written a scalar fuction to a table fucntion, however there is a small problem. Within the scalar function, there is a section where the return value is set to null, this means that if no value gets assigned to it within function logic, it will return null. It is however proving problematic transferring thesame login to a table function since I cannot assign variables.

    I need a way to check whereby if the select statement returns nothing from inline query, then simply return null. Is there a built in SLQ function similar to isnull that can do this ?

    Wednesday, July 31, 2013 4:11 PM

Answers

  • The key to making this work is that the two sections of the UNION ALL need to be mutually exclusive.  So something like this should work.

    CREATE  FUNCTION [dbo].[fnGetStockID](
    	@InstrumentID int, 
    	@RevisedDate datetime,
    	@ValuedOnly bit,  
    	@RiskRated bit  
    )
    RETURNS TABLE
    AS 
    RETURN
    SELECT Stk.StockID
    FROM tblStockValues Stk with (nolock)
    where Stk.InstrumentID = @InstrumentID
    and (DATEDIFF(dd,Stk.RevisionDate,@RevisedDate) = 0)
    and Stk.RevisionNo 
    = (
    select max(Stk2.RevisionNo) from tblStockValues Stk2 with (nolock)
    where Stk2.InstrumentID = Stk.InstrumentID
    and (DATEDIFF(dd,Stk2.RevisionDate ,Stk.RevisionDate) = 0)
    AND (@ValuedOnly = 0 OR Stk2.Aggregated = @ValuedOnly) 
    AND (@RiskRated = 0 OR Stk2.Official = @RiskRated)
    )
    
    UNION ALL
    
    SELECT NULL
    WHERE NOT EXISTS
    	(
    	SELECT Stk.StockID
    	FROM tblStockValues Stk with (nolock)
    	where Stk.InstrumentID = @InstrumentID
    	and (DATEDIFF(dd,Stk.RevisionDate,@RevisedDate) = 0)
    	and Stk.RevisionNo 
    	= (
    	select max(Stk2.RevisionNo) from tblStockValues Stk2 with (nolock)
    	where Stk2.InstrumentID = Stk.InstrumentID
    	and (DATEDIFF(dd,Stk2.RevisionDate ,Stk.RevisionDate) = 0)
    	AND (@ValuedOnly = 0 OR Stk2.Aggregated = @ValuedOnly) 
    	AND (@RiskRated = 0 OR Stk2.Official = @RiskRated)
    	)
    )

    Alternately using LEFT OUTER JOIN or OUTER APPLY in the query using the TVF should also work.
    Thursday, August 1, 2013 3:04 PM

All replies

  • You cannot transfer this logic from scalar funtion to table function because the usage and the role of the table function is completly different. Table function is like a parametrized view and must be stand after "FROM" clause or in JOINs.

    Table function cannot return null value it can return empty resultset instead. You can interpret the empty resultset as null return value. Or you can generate a resultset within a function with only one row with full of NULL values in all columns in case of null returning scalar function.


    m@te


    • Edited by farkas.mate Wednesday, July 31, 2013 4:22 PM
    Wednesday, July 31, 2013 4:21 PM
  • if the select statement returns nothing from inline query, then simply return null.


    In a TDF you would return nothing (= no row) instead.

    Olaf Helper

    Blog Xing

    Wednesday, July 31, 2013 4:29 PM
  • You could use the UNION ALL syntax to simulate this in an inline table-valued function.  Here's a simple example to demonstrate the principle:

    USE tempdb
    GO
    
    CREATE FUNCTION dbo.utf_simpleTVF
    (
        @object_id INT
    )
    RETURNS TABLE AS RETURN
    (
    	SELECT object_id, name
    	FROM sys.tables
    	WHERE object_id = @object_id
    
    	UNION ALL
    
    	SELECT NULL, NULL
    	WHERE NOT EXISTS
    		(
    		SELECT object_id, name
    		FROM sys.tables
    		WHERE object_id = @object_id
    		)
    )
    GO
    
    -- Example 1, with valid input parameter
    -- function will return single row
    DECLARE @object_id INT = ( SELECT TOP 1 object_id FROM sys.tables )
    
    SELECT * FROM dbo.utf_simpleTVF(@object_id)
    GO
    
    -- Example 2, with null input parameter; 
    -- function will return single row with NULLs
    DECLARE @object_id INT = NULL
    
    SELECT * FROM dbo.utf_simpleTVF(@object_id)
    GO
    
    -- Cleanup
    IF OBJECT_ID('dbo.utf_simpleTVF') IS NOT NULL DROP FUNCTION dbo.utf_simpleTVF
    GO

    Wednesday, July 31, 2013 4:52 PM
  • >> I have just re-written a scalar function to a table function, however there is a small problem. <<

    Yes, there is! You did not post any DDL! How do you expect anyone to help you? Your vague narrative is scary. Of course SQL programmers hate to use UDFs of any kind. This is a highly standardized, set-oriented declarative language, while UDFs are procedural, proprietary and cannot be optimized or ported.  

    >> Within the scalar function, there is a section where the return value is set to NULL, this means that if no value gets assigned to it within function logic, it will return NULL. <<

    In SQL, the goal is to write a solution in one statement.The  pre-SQL, procedural programming languages have “sections” or modules with control structures (IF-THEN, WHILE, recursion, etc). Different mindset. 

    >> I need a way to check whereby if the SELECT statement returns nothing from inline query, then simply return NULL. Is there a built in SQL function similar to ISNULL (sic: SQL programmers use COALESCE() today) that can do this? <<

    A single column, empty result set is cast to a NULL. Sure wish we had code to look at ..

    --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

    Wednesday, July 31, 2013 5:58 PM
  • In SQL, the goal is to write a solution in one statement.

    Nothing on this Earth could be further from the truth.  Even YOU don't follow that recommendation.

    --Jeff Moden

    Thursday, August 1, 2013 1:47 AM
  • Hi,

    I have just re-written a scalar fuction to a table fucntion, however there is a small problem. Within the scalar function, there is a section where the return value is set to null, this means that if no value gets assigned to it within function logic, it will return null. It is however proving problematic transferring thesame login to a table function since I cannot assign variables.

    I need a way to check whereby if the select statement returns nothing from inline query, then simply return null. Is there a built in SLQ function similar to isnull that can do this ?

    You can also use Case, iif to detect null and to further return values. Read

    http://msdn.microsoft.com/en-us/library/ms181765.aspx

    http://msdn.microsoft.com/en-us/library/hh213574.aspx


    Many Thanks & Best Regards, Hua Min


    Thursday, August 1, 2013 3:52 AM
  • I have tried to rewrite the function, and I used the UNION ALL option, however during testing I noticed that in some cases 2 values were returned, the correct StockID and also a null value. It should only be one for a match and null for a non match. I have attached the code below.

    CREATE  FUNCTION [dbo].[fnGetStockID](
    @InstrumentID int, 
    @RevisedDate datetime,
    @ValuedOnly bit,  
    @RiskRated bit 	 
    )
    RETURNS int
    AS
    BEGIN
    
    DECLARE @StockID int
    
    SET @StockID = NULL  
    SELECT @StockID = Stk.StockID
    FROM tblStockValues Stk with (nolock)
    where Stk.InstrumentID = @InstrumentID
    and (DATEDIFF(dd,Stk.RevisionDate,@RevisedDate) = 0)
    and Stk.RevisionNo 
    = 	(
    	select max(Stk2.RevisionNo) from tblStockValues Stk2 with (nolock)
    	where Stk2.InstrumentID = Stk.InstrumentID
    	and (DATEDIFF(dd,Stk2.RevisionDate ,Stk.RevisionDate) = 0)
    	AND (@ValuedOnly = 0 OR Stk2.Aggregated = @ValuedOnly) 
    	AND (@RiskRated = 0 OR Stk2.Official = @RiskRated) 
    
    )
    	
    RETURN @StockID
    
    END
    

    Thursday, August 1, 2013 12:10 PM
  • CREATE  FUNCTION [dbo].[fnGetStockID](
    @InstrumentID int, 
    @RevisedDate datetime,
    @ValuedOnly bit,  
    @RiskRated bit  
    )
    RETURNS TABLE
    AS 
    RETURN
    SELECT Stk.StockID
    FROM tblStockValues Stk with (nolock)
    where Stk.InstrumentID = @InstrumentID
    and (DATEDIFF(dd,Stk.RevisionDate,@RevisedDate) = 0)
    and Stk.RevisionNo 
    = (
    select max(Stk2.RevisionNo) from tblStockValues Stk2 with (nolock)
    where Stk2.InstrumentID = Stk.InstrumentID
    and (DATEDIFF(dd,Stk2.RevisionDate ,Stk.RevisionDate) = 0)
    AND (@ValuedOnly = 0 OR Stk2.Aggregated = @ValuedOnly) 
    AND (@RiskRated = 0 OR Stk2.Official = @RiskRated) 

    )


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, August 1, 2013 12:26 PM
    Answerer
  • I have already written that solution, however I need it to return a null when a match isnt found. ITs tricky to do within a table fucntion but easier with scalar as you can assign within a scalar function.
    Thursday, August 1, 2013 2:39 PM
  • The key to making this work is that the two sections of the UNION ALL need to be mutually exclusive.  So something like this should work.

    CREATE  FUNCTION [dbo].[fnGetStockID](
    	@InstrumentID int, 
    	@RevisedDate datetime,
    	@ValuedOnly bit,  
    	@RiskRated bit  
    )
    RETURNS TABLE
    AS 
    RETURN
    SELECT Stk.StockID
    FROM tblStockValues Stk with (nolock)
    where Stk.InstrumentID = @InstrumentID
    and (DATEDIFF(dd,Stk.RevisionDate,@RevisedDate) = 0)
    and Stk.RevisionNo 
    = (
    select max(Stk2.RevisionNo) from tblStockValues Stk2 with (nolock)
    where Stk2.InstrumentID = Stk.InstrumentID
    and (DATEDIFF(dd,Stk2.RevisionDate ,Stk.RevisionDate) = 0)
    AND (@ValuedOnly = 0 OR Stk2.Aggregated = @ValuedOnly) 
    AND (@RiskRated = 0 OR Stk2.Official = @RiskRated)
    )
    
    UNION ALL
    
    SELECT NULL
    WHERE NOT EXISTS
    	(
    	SELECT Stk.StockID
    	FROM tblStockValues Stk with (nolock)
    	where Stk.InstrumentID = @InstrumentID
    	and (DATEDIFF(dd,Stk.RevisionDate,@RevisedDate) = 0)
    	and Stk.RevisionNo 
    	= (
    	select max(Stk2.RevisionNo) from tblStockValues Stk2 with (nolock)
    	where Stk2.InstrumentID = Stk.InstrumentID
    	and (DATEDIFF(dd,Stk2.RevisionDate ,Stk.RevisionDate) = 0)
    	AND (@ValuedOnly = 0 OR Stk2.Aggregated = @ValuedOnly) 
    	AND (@RiskRated = 0 OR Stk2.Official = @RiskRated)
    	)
    )

    Alternately using LEFT OUTER JOIN or OUTER APPLY in the query using the TVF should also work.
    Thursday, August 1, 2013 3:04 PM
  • This is the goal of any declarative or functional programming model. Chris Date summarized this principle is the catch phrase "Tell me what, not how!" as the title of one of his essays and books. While I am not a LISP programmers, you can get a good intro in "An Introduction to Functional Programming Through Lambda Calculus" or talk to an APL programmer.

    Oh, did you wonder why we invented the MERGE statement? We got tired of control flow logic with separate UPDATE and INSERT statements. 

    --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

    Thursday, August 1, 2013 5:15 PM
  • Did you know that the prefix “fn-” on user functions is part of FORTRAN II? It was required to signal those early compilers what to do. We also had to start integer variables with the letters I to N and some systems required prefixes on various physical data sources (Tnn for tape drives, etc). This is why noobs commit the error of “tibbling”; it means using “tbl-” or other form of it in a table name. 

    Today, this is a violation of ISO-11179 rules, and basic data modeling. Why do you use verbs and adjectives for the names of columns? That is so fundamentally wrong! “Official_<something>” or “<something>_aggregation” might make sense.  

    Also, we do not use BIT flags in SQL. That was Assembly language programming. SQL is a predicate language, so we use logical expressions to determine the current state of the data. For example, an SQL programmer might create a “risk_rating” attribute, using an industry standard scale and perhaps a timestamp for this rating. He would never destroy data with a 1950's BIT flag. 

    Another piece of FORTRAN history! Did you ever wonder why you have to write:

    BEGIN
    DECLARE @local_register INTEGER; 
     << bunch of code>>;
    SET @local_register = <scalar expression>; 
    RETURN @local_register;
    END;

    The first FORTRAN compilers from IBM had to use hardware registers to pass parameters to function subroutines. T-SQL copied this 1950's compiler architecture. Programming languages with orthogonality would simply have “RETURN (<scalar expression>)” instead. 

    The expression:

    (DATEDIFF(DD, S.revision_date, @in_revision_date) = 0)

    Should have been (S.revision_date = @in_revision_date) because you would have declared the column to be a DATE data type. This expression can be optimize, and yours cannot. Unlike FORTRAN, 80% to 95% of the work in SQL is done in the DDL. 

    You are writing 1950 – 1960's FORTRAN in T-SQL and probably do not even know it. An SQL programmer would use a VIEW and then write queries against it. Unlike tape mfiels, thre is no need to physically materialize data! Again, without DDL, my guess is a simple VIEW like this ti show the most current revisions might be useful:

    CREATE VIEW Current_Stock 
    AS 
    WITH X
    AS
    (SELECT S1.stock_id, S1.revision_date, 
           MAX(S1.revision_date) OVER (PARTITION BY S1.stock_id)
           AS rev_date_max
      FROM StockValues AS S1)

    SELECT S2.* -- actually list  columns in real code
      FROM StockValues AS S2
     WHERE S2.revision_date = S2.rev_date_max; 

    Get ready to throw out what you had done and start over. The database will be smaller, run faster and you will not have to keep posting for kludges 


    --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

    Thursday, August 1, 2013 6:10 PM