locked
Replacing scalar function with table function RRS feed

  • Question

  • I have an sproc that uses a scalar valued function (with parameters) to return a value for a column. Now, I find I need to return a second value. One option is to create an identical scalar valued function that returns the second value. Is there a better way to do this, perhaps through a table valued function? If so, I'm not sure of the syntax.
    Monday, November 12, 2012 5:10 PM

Answers

  • Yes, it should be able to use values from the columns. The code you have looks correct to me, that is why I suggested re-typing it as perhaps there is something I don't see. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by JimP46 Monday, November 12, 2012 10:08 PM
    Monday, November 12, 2012 9:45 PM
  • For better perfomance  consider rewriting your function body in a style
    	--COMMENT
    	--O = Office
    	--C = Client
    	--D = Division	
    	--P = Product
    
    	;with ISF as 
    	(select * from dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    	)
    	INSERT INTO @table
    	SELECT 
    		COALESCE(ocdp.STD_COMMENT, ocdp2.STD_COMMENT, ... , o2.STD_COMMENT),		
    		COALESCE(ocdp.FONT_SIZE, ocdp2.FONT_SIZE, ... , o2.FONT_SIZE)
    	FROM  	    
    	(SELECT STD_COMMENT, FONT_SIZE FROM  ISF 
    		WHERE  OFFICE_CODE = @office_code
    			AND CLIENT_CODE = @client_code
    			AND DIVISION_CODE = @division_code
    			AND PRODUCT_CODE = @product_code ) ocdp
    	outer apply
    	(SELECT STD_COMMENT,FONT_SIZE FROM ISF 
    		WHERE OFFICE_CODE IS NULL
    			AND CLIENT_CODE = @client_code
    			AND DIVISION_CODE = @division_code
    			AND PRODUCT_CODE = @product_code ) ocdp2 	
    	...
    	outer apply 
    	(SELECT STD_COMMENT 
    	     , case when STD_COMMENT IS NOT NULL then FONT_SIZE else null end FONT_SIZE
    	    FROM ISF 
    		WHERE OFFICE_CODE IS NULL
    		AND CLIENT_CODE IS NULL
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL ) o2 
    
        RETURN
    
    And as a next step, you may wish make ISF a permanent view and then you needn't the function any more, you can incorporate above subquery into any other query.

    Serg

    • Marked as answer by JimP46 Tuesday, November 13, 2012 12:58 PM
    Tuesday, November 13, 2012 9:59 AM

All replies

  • It will help if you give the DDL for your current scalar function.  Also, give a look at books online for the syntax for a table function.  What version of SQL Server are you using?
    Monday, November 12, 2012 5:19 PM
  • Hi Jim,

    You can use table-valued function syntax as below :

    http://msdn.microsoft.com/en-us/library/ms191165(v=sql.105).aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    Monday, November 12, 2012 5:22 PM
  • please post your scalar function.

    sample :

    CREATE FUNCTION [dbo].fnname
    (
    @input1 int,
    @input2 int
    )
    RETURNS @Outputtable Table (id int ,InvoiceID int )
    AS
    BEGIN

    Insert into @Outputtable (Id,InvoiceID )
    Select ID,InvoiceID 
    From dbo.Invoice inv
    where ID = @input1 and InvoiceID = @input2

    RETURN

    END

    -- 

    select * from fnname(@input1,@input2)

    Monday, November 12, 2012 5:51 PM
  • Tried to post code by simply copying and pasting, and received message too many characters. Is there another way to post?
    Monday, November 12, 2012 6:41 PM
  • I guess my question is, "Is it posible to join to a table valued function that excepts parameters?" (no problem joining to one that doesn't have parameters)

    Monday, November 12, 2012 6:47 PM
  • Tried to post code by simply copying and pasting, and received message too many characters. Is there another way to post?
    Did you try the little gizmo to the top that says "Insert Code Block"?

    Pérez

    Monday, November 12, 2012 6:50 PM
  • Thanks,

    This is simple enough. But, how would I join to this function (or otherwise use it) to retrieve the values for "ID" and "InvoiceID" in an sproc?

    Monday, November 12, 2012 6:50 PM
  • It is impossible to debug code without seeing it. Minimal Netiquette is to post at least the DDL. 

    Having said that, we hate scalar and table-valued functions. They are a bitch to optimize for the SQL engine, a bitch to maintain for the company, and do not port. MS has them, so that non-SQL programmers can feel comfortable writing C# or VB style procedural code in T-SQL. Some of these guys still prefix their functions with the old "fn_" or similar prefixes from early versions of BASIC! 

    SQL programmers prefer declarative programming with expressions. 


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

    • Proposed as answer by Naomi N Monday, November 12, 2012 7:08 PM
    Monday, November 12, 2012 6:54 PM
  • Definitley, TVF perfectly fits to the task

    Suppose you have:

    update someTbl set r= myFunction(t1.x, t2.y)

    from someTbl t1 join otherTbl t2 on ...

    You need :

    update someTbl set r=myFunction(t1.x, t2.y), s= ... --- similar function

    Then refactor  myFunction to TVF which will return the only row with two columns, and get kind of :

    update someTbl set r= F.x, s= F.y

    from someTbl t1 join otherTbl t2 on ...

    cross apply myFunction(t1.x, t2.y) F

    See http://msdn.microsoft.com/en-us/library/ms175156(v=sql.105).aspx


    Serg


    • Edited by SergNL Monday, November 12, 2012 6:58 PM
    Monday, November 12, 2012 6:57 PM
  • Thank you,

    This should work, but I'm getting a syntax error - would like to post my code, but can't find a way to do it (cut and paste not working).

    Monday, November 12, 2012 7:36 PM
  • Thanks,

    ..need some help posting code - not sure how to do it?

    Monday, November 12, 2012 7:38 PM
  • There is a insert code button (the last button before the 'image' button on the toolbar when you create a message).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 12, 2012 7:44 PM
  • Thank you - so simple when you find it.
    Monday, November 12, 2012 7:58 PM
  • Thank you,

    The "OUTER APPLY" statement works when I provide literal values (next to last line). I get a syntax error when I provide variables (last line). Below is my code sequence. Any suggestions?

    SELECT 
    		jl.JOB_NUMBER,
    		jl.CL_CODE,
    		jl.DIV_CODE,
    		jl.PRD_CODE,
    		jl.CMP_CODE,
    		jl.CMP_IDENTIFIER,
    		jl.SC_CODE,
    		jl.JOB_DESC, 
    		jl.JOB_COMMENTS,
    		jl.JOB_CLI_REF,
    		jl.COMPLEX_CODE,
    		jl.PROMO_CODE,
    		jl.JOB_BILL_COMMENT,
    		sc.SC_DESCRIPTION,
    		ISNULL(p.PRD_CONSOL_FUNC,0) AS PRD_CONSOL_FUNC,
    		cmt2.std_comment,
    		cmt2.font_size
    	FROM dbo.JOB_LOG AS jl
    	JOIN #ar_inv_joblist aij
    		ON jl.JOB_NUMBER = aij.JOB_NUMBER
    	JOIN dbo.SALES_CLASS sc
    		ON jl.SC_CODE = sc.SC_CODE
    	JOIN dbo.PRODUCT p
    		ON jl.CL_CODE = p.CL_CODE
    		AND jl.DIV_CODE = p.DIV_CODE
    		AND jl.PRD_CODE = p.PRD_CODE
    	JOIN dbo.CLIENT AS c
    		ON jl.CL_CODE = c.CL_CODE	
    	--OUTER APPLY dbo.advtf_std_invoice_comment('main', 'abc', 'abc', 'abc') AS cmt2	
    	OUTER APPLY dbo.advtf_std_invoice_comment(jl.OFFICE_CODE, jl.CL_CODE, jl.DIV_CODE, jl.PRD_CODE) AS cmt2

    Monday, November 12, 2012 8:08 PM
  • What is the exact error message and what is the text for the function.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 12, 2012 8:29 PM
  • Error message is below (OUTER APPLY statement),

    "Msg 102, Level 15, State 1, Procedure advsp_ar_job_header_comments, Line 78

    Incorrect syntax near '.'."

    Code for function is below.

    -- advtf_std_invoice_comment
    -- #00 09/12/12 - Initial release
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[advtf_std_invoice_comment]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[advtf_std_invoice_comment]
    GO
    CREATE FUNCTION [dbo].[advtf_std_invoice_comment](
    	@office_code varchar(6), 
    	@client_code varchar(6), 
    	@division_code varchar(6),
    	@product_code varchar(6))
    		
    RETURNS @table TABLE (
    	--std_comment varchar(4000),
    	std_comment text,
    	font_size smallint)
    AS
    BEGIN
    	--COMMENT
    	--O = Office
    	--C = Client
    	--D = Division	
    	--P = Product
    	--Comment for OCDP
    	DECLARE @comment_ocdp varchar(4000)
    	SELECT @comment_ocdp = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE = @product_code)
    	--Comment for OCDP2 (null office)
    	DECLARE @comment_ocdp2 varchar(4000)
    	SELECT @comment_ocdp2 = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE = @product_code)
    	--Comment for OCD	
    	DECLARE @comment_ocd varchar(4000)
    	SELECT @comment_ocd = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE IS NULL)
    	--Comment for OCD2 (null office)	
    	DECLARE @comment_ocd2 varchar(4000)
    	SELECT @comment_ocd2 = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE IS NULL)
    	--Comment for OC	
    	DECLARE @comment_oc varchar(4000)
    	SELECT @comment_oc = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL)
    	--Comment for OC2 (null office)	
    	DECLARE @comment_oc2 varchar(4000)
    	SELECT @comment_oc2 = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL)
    	--Comment for O
    	DECLARE @comment_o varchar(4000)
    	SELECT @comment_o = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE IS NULL
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL)
    	--Comment for O2 (null office - default)
    	DECLARE @comment_o2 varchar(4000)
    	SELECT @comment_o2 = (SELECT STD_COMMENT FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE IS NULL
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL)
    	
    	--FONT SIZE	
    	--Font Size for OCDP
    	DECLARE @font_size_ocdp varchar(4000)
    	SELECT @font_size_ocdp = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE = @product_code)
    	--Font Size for OCDP2 (null office)
    	DECLARE @font_size_ocdp2 varchar(4000)
    	SELECT @font_size_ocdp2 = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE = @product_code)
    	--Font Size for OCD	
    	DECLARE @font_size_ocd varchar(4000)
    	SELECT @font_size_ocd = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE IS NULL)
    	--Font Size for OCD2	 (null office)
    	DECLARE @font_size_ocd2 varchar(4000)
    	SELECT @font_size_ocd2 = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE = @division_code
    		AND PRODUCT_CODE IS NULL)
    	--Font Size for OC	
    	DECLARE @font_size_oc varchar(4000)
    	SELECT @font_size_oc = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL)
    	--Font Size for OC2 (null office)	
    	DECLARE @font_size_oc2 varchar(4000)
    	SELECT @font_size_oc2 = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE = @client_code
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL)
    	--Font Size for O
    	DECLARE @font_size_o varchar(4000)
    	SELECT @font_size_o = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE = @office_code
    		AND CLIENT_CODE IS NULL
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL)
    	--Font Size for O2 (null office - default)
    	DECLARE @font_size_o2 varchar(4000)
    	SELECT @font_size_o2 = (SELECT FONT_SIZE FROM dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    		AND OFFICE_CODE IS NULL
    		AND CLIENT_CODE IS NULL
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL
    		AND STD_COMMENT IS NOT NULL)	
    	
    	INSERT INTO @table
    	SELECT 
    		COALESCE(@comment_ocdp, @comment_ocdp2, @comment_ocd, @comment_ocd2, @comment_oc, @comment_oc2, @comment_o, @comment_o2),		
    		COALESCE(@font_size_ocdp, @font_size_ocdp2, @font_size_ocd, @font_size_ocd2, @font_size_oc, @font_size_oc2, @font_size_o, @font_size_o2)
    	RETURN
    END

    Monday, November 12, 2012 8:59 PM
  • I don't see the error. What happens when you double click on that error message?

    If you don't see the error, I suggest to delete last statement and re-type. Perhaps you typed something incorrectly there which is hard to see in the forum.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 12, 2012 9:07 PM
  • Thank you, but I may confusing things. The error that I'm getting is "Incorrect syntax near '.'" in the "OUTER APPLY" statement (last line below). This is in the sproc that calls the function.

    Can you tell me if an "OUTER APPLY" statement should accept variables for the function? (the error goes away if I replace the variables with literals.)

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

    FROM dbo.JOB_LOG AS jl
    JOIN
    #ar_inv_joblist aij
    ON jl
    .JOB_NUMBER = aij.JOB_NUMBER
    JOIN dbo
    .SALES_CLASS sc
    ON jl
    .SC_CODE = sc.SC_CODE
    JOIN dbo
    .PRODUCT p
    ON jl
    .CL_CODE = p.CL_CODE
    AND jl
    .DIV_CODE = p.DIV_CODE
    AND jl
    .PRD_CODE = p.PRD_CODE
    JOIN dbo
    .CLIENT AS c
    ON jl
    .CL_CODE = c.CL_CODE
    OUTER APPLY dbo.advtf_std_invoice_comment(jl.OFFICE_CODE, jl.CL_CODE, jl.DIV_CODE, jl.PRD_CODE) AS cmt2

    Monday, November 12, 2012 9:37 PM
  • Yes, it should be able to use values from the columns. The code you have looks correct to me, that is why I suggested re-typing it as perhaps there is something I don't see. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by JimP46 Monday, November 12, 2012 10:08 PM
    Monday, November 12, 2012 9:45 PM
  • Thanks - I'll give it a try.
    Monday, November 12, 2012 10:08 PM
  • For better perfomance  consider rewriting your function body in a style
    	--COMMENT
    	--O = Office
    	--C = Client
    	--D = Division	
    	--P = Product
    
    	;with ISF as 
    	(select * from dbo.STD_COMMENT 
    		WHERE APP_CODE = 'Invoices'
    		AND COMMENT_TYPE = 'Standard Footer'
    	)
    	INSERT INTO @table
    	SELECT 
    		COALESCE(ocdp.STD_COMMENT, ocdp2.STD_COMMENT, ... , o2.STD_COMMENT),		
    		COALESCE(ocdp.FONT_SIZE, ocdp2.FONT_SIZE, ... , o2.FONT_SIZE)
    	FROM  	    
    	(SELECT STD_COMMENT, FONT_SIZE FROM  ISF 
    		WHERE  OFFICE_CODE = @office_code
    			AND CLIENT_CODE = @client_code
    			AND DIVISION_CODE = @division_code
    			AND PRODUCT_CODE = @product_code ) ocdp
    	outer apply
    	(SELECT STD_COMMENT,FONT_SIZE FROM ISF 
    		WHERE OFFICE_CODE IS NULL
    			AND CLIENT_CODE = @client_code
    			AND DIVISION_CODE = @division_code
    			AND PRODUCT_CODE = @product_code ) ocdp2 	
    	...
    	outer apply 
    	(SELECT STD_COMMENT 
    	     , case when STD_COMMENT IS NOT NULL then FONT_SIZE else null end FONT_SIZE
    	    FROM ISF 
    		WHERE OFFICE_CODE IS NULL
    		AND CLIENT_CODE IS NULL
    		AND DIVISION_CODE IS NULL
    		AND PRODUCT_CODE IS NULL ) o2 
    
        RETURN
    
    And as a next step, you may wish make ISF a permanent view and then you needn't the function any more, you can incorporate above subquery into any other query.

    Serg

    • Marked as answer by JimP46 Tuesday, November 13, 2012 12:58 PM
    Tuesday, November 13, 2012 9:59 AM
  • Thank you - I'll give it a try.
    Tuesday, November 13, 2012 12:58 PM