Defining parameters in table valued function - SQL Server

Question

I am trying to return matching zip codes as a table, so I can use it with 'Where zipCode IN(...) statement.

create function dbo.zipSearch(@zip varchar(12), @mile int)
  returns table
  as
  begin
    declare @ns float = @mile * 0.00569;
    declare @ew float = @mile * 0.01629;

    declare @ltt float, @lng float;
    Select @ltt = latitude, @lng = longitude From ZipCode Where ZIP = @zip;

    Select ZIP From zipcode Where latitude >= @ltt - @ns and latitude <= @ltt + @ns and longitude >= @lng - @ew and longitude <= @lng + @ew;

    return
  end

What would be the alternative if this is not possible?

Your function is likely to have some pretty extreme performance problems. What you have here is a multi statement table valued function and is often going to perform worse than a scalar function. You should change this into an inline table valued function. That means a single query and nothing more. — Sean Lange
– Sean Lange, Commented Jan 19, 2016 at 20:12
What you want to do is possible. You need to include a table definition after "returns table". Please see the documentation of create function for examples. — Tab Alleman
– Tab Alleman, Commented Jan 19, 2016 at 20:13
@TabAlleman thanks for the tip.. the alternative syntax solved the problem. — David Duman
– David Duman, Commented Jan 19, 2016 at 20:17

Community · Accepted Answer · 2017-05-23 12:15:16Z

Here is how you could turn this into an inline table valued function. The performance benefits might surprise you.

create function dbo.zipSearch(@zip varchar(12), @mile int)returns table as return
    Select ZIP 
    From zipcode 
    cross apply
    (
        select latitude
            , longitude 
        from ZipCode 
        where Zip = @zip
    ) LatLong 
    Where latitude >= LatLong.latitude - (@mile * 0.00569) 
        and latitude <= LatLong.latitude + (@mile * 0.00569) 
        and longitude >= LatLong.longitude - (@mile * 0.01629) 
        and longitude <= LatLong.longitude + (@mile * 0.01629);

Here are just a few articles about the differences.

Multi-statement Table Valued Function vs Inline Table Valued Function

http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/

http://sqlmag.com/t-sql/inline-vs-multistatement-table-valued-udfs

Collectives™ on Stack Overflow

Defining parameters in table valued function - SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related