none
view vs table - valued function

    Question

  • Dear all again:) hope every1 is fine :) question

    say you have this query

    SELECT manipud.gridis, itrig.intid
    from
    dbo.manipud inner join dbo.itrig
    ON manipud.id = intid.id
    

    now say itrig is the view. this will work fine, but what happened was...i couldnt make itrig a view because i had to add variables and was advised to make it into a table value function. so running this code gives me an error

     

    Parameters were not supplied for the function 'dbo.itrig'.

     

    now this is a little trouble, cause i wanted to be able to query from itrig, like i would a view, and i cant make it a view cause it has variables and if i have to run this query like the one above...how do i pass in variables for itrig

     

    thNKs

     

    Monday, February 06, 2012 4:56 PM

Answers

  • If you're passing parameters which are not fields from the tables (unrelated constants, as in your case), then there is no need for the CROSS APPLY. You only need to use CROSS APPLY when you need to apply a function for each row in a table.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Gert-Jan Strik Monday, February 06, 2012 7:06 PM
    • Marked as answer by KJian_ Monday, February 13, 2012 6:33 AM
    Monday, February 06, 2012 6:18 PM
  • If you need these parameters in your function, yes, you need to declare them as parameters in the function. And you declare them in the procedure as well. I don't see why does it present a problem.

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


    My blog

    • Marked as answer by KJian_ Monday, February 13, 2012 6:32 AM
    Tuesday, February 07, 2012 2:13 PM

All replies

  • You can design a INLINE table-valued function with a single parameter like follows:

    CREATE FUNCTION fnITRIG (@PARM VARCHAR (64))
    RETURNS TABLE
    AS
    RETURN (SELECT manipud.gridis, itrig.intid
            from  dbo.manipud inner join dbo.itrig
            ON manipud.id = intid.id
            WHERE YOURCOLUMN = @PARM)
    GO
    
    

     

    UDF article with examples:

    http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

    Example for using a table-valued UDF in CROSS APPLY:

    http://www.sqlusa.com/articles2005/crossapply/ 


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Monday, February 06, 2012 5:06 PM
  • Try to create function like below :

    create function dbo.itrig(...) 
    returns @T table (id int,...)
    as
    begin
    ..
    ..
    return
    end
    ----
    ----
    SELECT manipud.gridis, itrig.intid
    from
    dbo.manipud inner join dbo.itrig it
    ON manipud.id = it.id
    

     

     

    Best regards
    Monday, February 06, 2012 5:08 PM
  • no no i think i didnt explain properly

     

    dbo.itrig

    is a table function, already created and defined and everything

    Monday, February 06, 2012 5:11 PM
  • You need to use CROSS APPLY to execute table-valued function against a field in a table.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 06, 2012 5:16 PM
  • and still pass in parameters?

    Monday, February 06, 2012 5:29 PM
  • Yes, affirmative.

    Take a look at the CROSS APPLY examples in the following article:

    http://www.sqlusa.com/bestpractices2005/crossapply/

    Pretty powerful stuff. Started with SQL Server 2005.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Monday, February 06, 2012 5:38 PM
  • The parameters will be the fields from the table in case of CROSS APPLY. If your table valued function requires parameters, you MUST pass parameters.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 06, 2012 5:55 PM
  • CREATE table dbo.fun_test(
    name nvarchar(5),
    id nvarchar(5),
    prem numeric(10),
    seco numeric(10),
    )
    GO
    insert into dbo.fun_test
    VALUES ('john','100',1,2),('janic','200',4,2),('timi','300',1,2)
    Go
    
    CREATE table dbo.cross_test(
    name nvarchar(5),
    id nvarchar(5),
    prem numeric(10),
    seco numeric(10),
    )
    GO
    insert into dbo.cross_test
    VALUES ('peter','100',1,2),('jane',200,4,2),('katie',300,1,2)
    Go
    
    
    
    CREATE FUNCTION fnITRIG (@one numeric(10),@two numeric(10))
    RETURNS TABLE
    AS
    RETURN (SELECT  fun_test.name, fun_test.id,fun_test.prem,fun_test.seco
    FROM    dbo.fun_test
    WHERE   fun_test.prem = @one AND fun_test.seco=@two )
    GO
    
    
    --select fnITRIG.name from dbo.fnITRIG(1,2)
    
    select cross_test.name,cross_test.id,fnITRIG.name as function_name,fnITRIG.id as function_id
    from dbo.cross_test cross apply fnITRIG(1,2)
    
    /*
    select cross_test.name,cross_test.id,fnITRIG.name as function_name,fnITRIG.id as function_id
    from dbo.cross_test inner join fnITRIG(1,2)
    on cross_test.id=fnITRIG.id
    */
    
    
    
    
    /*
    drop table dbo.fun_test
    drop table dbo.cross_test
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnITRIG]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fnITRIG]
    GO
    */
    

    This...is probably what im looking for...but...inner join seems to give me the correct results and not the cross apply..feel free to copy and run in you SSMS. thanks
    Monday, February 06, 2012 6:12 PM
  • If you're passing parameters which are not fields from the tables (unrelated constants, as in your case), then there is no need for the CROSS APPLY. You only need to use CROSS APPLY when you need to apply a function for each row in a table.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Gert-Jan Strik Monday, February 06, 2012 7:06 PM
    • Marked as answer by KJian_ Monday, February 13, 2012 6:33 AM
    Monday, February 06, 2012 6:18 PM
  • ok but also...can you propose something that can get a similar job done? i dont like passing in variables for every single variable this is sooo tedious :(
    Monday, February 06, 2012 7:43 PM
  • Can you provide your complete business requirements and what you're trying to achieve without having us answering the vague questions?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 06, 2012 7:46 PM
  • You can get more assistance on database driven subscription at:

    SQL Server Reporting Services

    If you mean parametrized report dataset, then stored procedure is your best choice:

    Designing Reports with SQL Server Reporting Services 2005

    SSRS has built-in support for stored procedure (with or without parameter) as data source.

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Monday, February 06, 2012 8:44 PM
  • can u look at this code again please.

     

    as you can see i hard coded the (1,2) in

    select cross_test.name,cross_test.id,fnITRIG.name as function_name,fnITRIG.id as function_id
    from dbo.cross_test inner join fnITRIG(1,2)
    on cross_test.id=fnITRIG.id
    


    cant i pass something like

    fnITRIG(@one,@two)

    cause im thinking like... if you have to do the query in a report....where is fnITRIG going to be set

    Monday, February 06, 2012 8:53 PM
  • Yes, you can use parameters in the above code, 1 and 2 do not need to be constants.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 06, 2012 8:55 PM
  • but i cant say, im going to have to declare @one and @two which has already been done in fnTrig i dont want to do it here again...

    create procedure dbo.usptrig
    select cross_test.name,cross_test.id,fnITRIG.name as function_name,fnITRIG.id as function_id
    from dbo.cross_test inner join fnITRIG(@one,@two)
    on cross_test.id=fnITRIG.id

    must i do

    create function dbo.fnnerep

    (

    and declare @one and @two from fnITRIG again?

    • Edited by Femininity Tuesday, February 07, 2012 1:31 PM
    Tuesday, February 07, 2012 1:19 PM
  • If you need these parameters in your function, yes, you need to declare them as parameters in the function. And you declare them in the procedure as well. I don't see why does it present a problem.

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


    My blog

    • Marked as answer by KJian_ Monday, February 13, 2012 6:32 AM
    Tuesday, February 07, 2012 2:13 PM
  • Maybe I miss your point, but why can't you use a View instead of a tabel valued function?

    Your function is just a SELECT statement with a where clause and the criterias doesn't seems to be related to the SELECT statement you want to join it with?

    Why not just use the view and then include the WHERE clause in the JOIN criteria or use a sub-query?

    Maybe something like this -

    select cross_test.name,cross_test.id, fun_test.*
    from dbo.cross_test JOIN fun_test ON cross_test.id = fun_test.id AND fun_test.prem = @one AND fun_test.seco = @two
    


    Steen Schlüter Persson (DK)

    Wednesday, February 08, 2012 8:13 AM