CODEWITHSUNDEEP
sql-serverfunction
DiPix
DiPix

Reputation: 6083

Function in SQL for JOIN condition

This is my query:

SELECT main.SomeValues, mainData.Name
  FROM dbo.MainTable main JOIN 
       dbo.MainDataTable mainData ON 
            (main.dataId = mainData.dataId) AND 
            (mainData.Type = 1 OR mainData.Type = 2 OR mainData.Type = 3)

I use similar query in many views. But the last condition is always the same everywhere: main.Type = 1 OR main.Type = 2 OR main.Type = 3.

I wondering how I can extract it to some SQL function. I never do any function before.

So it would be looks like this:

SELECT main.SomeValues, mainData.Name
FROM dbo.MainTable main
JOIN dbo.MainDataTable mainData ON main.dataId = mainData.dataId
       AND (GetConditionForType())  -- more or less ;)

Upvotes: 0

Views: 97

Answers (2)

sepupic
sepupic

Reputation: 8687

You can create a view like this:

  create view dbo.FilteredMainDataTable
  as
     select ...
     from dbo.MainDataTable 
     where [Type] in (1,2,3);

And then use this view in all your queries instead of dbo.MainDataTable. What the compiler does then it "opens" the view as its definition in every query that uses this view and that is what you want. Functions do not do this and they are not thought as "macro substitution"

If you insist on function you can create it but it will not have a "look" as you want. It can be inline table-valued function like this:

create function dbo.fn_FilteredMainDataTable(@n1 int, @n2 int, @n3 int)
returns TABLE
    return select Id, ...
    from FilteredMainDataTable
    where type in (@n1, @n2, @n3);

Then you join to this function instead of MainDataTable like this:

SELECT main.SomeValues, mainData.Name
FROM dbo.MainTable main
JOIN dbo.fn_FilteredMainDataTable(1,2,3) mainData ON main.dataId = mainData.dataId

^^^^^^^^^^^^^^^^^^^^^^^

The following code shows how inline table function like a view push in the seek predicate:

    if object_id('dbo.num') is not null drop table dbo.num;
    go

    select top 1000000
           isnull(row_number() over(order by 1 / 0), 0) as n,
           isnull(row_number() over(order by 1 / 0), 0) as n1
    into dbo.num
    from sys.columns c1 cross join sys.columns c2 cross join sys.columns c3;
    go

    alter table dbo.num add constraint PK_num_n primary key (n);
    go

    create index ix_n1_n on dbo.num (n1, n);
    go

    if object_id('dbo.fn_num_between') is not null drop function dbo.fn_num_between;
    go


    create function dbo.fn_num_between(@n1 int, @n2 int)
    returns table
    as
       return 
       select n, n1
       from dbo.num
       where n between @n1 and @n2;
    go

    select *
    from dbo.fn_num_between(1, 1000)
    where n1 = 5;

seek predicate

Upvotes: 1

Ashley Pillay
Ashley Pillay

Reputation: 888

You can do this with an inline table valued function. You would define the function as:

create function fnFilteredTypes ()
returns table as return
(
        select 1 as type
        union
        select 2 as type
        union
        select 3 as type

);

Then you can join against this function like so:

SELECT main.SomeValues, mainData.Name
FROM dbo.MainTable AS main
JOIN dbo.MainDataTable AS mainData
    ON main.dataId = mainData.dataId
JOIN fnFilteredTypes() As typeFilter
    ON(mainData.Type=typeFilter.type)

I'm not sure this query is really what you want, but basically you get the idea that you can use the function like it's a parameterized view & join directly against it's fields. Just don't forget it's a function & you call it with round brackets.

Upvotes: 0

Related Questions