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, Jan 19 '16 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, Jan 19 '16 at 20:13
@TabAlleman thanks for the tip.. the alternative syntax solved the problem. — dvdmn, Jan 19 '16 at 20:17

score 2 · Answer 1 · edited May 23 '17 at 12:15

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

score 0 · Answer 2 · answered Jan 19 '16 at 20:19

Posting the final version of the function in case someone else hits the same wall as I do. Thanks to @Tab Alleman for the tip. It was a syntax issue.

create function dbo.zipSearch(@zip varchar(12), @mile int)
  returns @tmp TABLE (zipCode varchar(12))
  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;

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

    return
  end

Please see my answer below and the articles referenced about turning this into an inline table valued function and the performance benefits. — Sean Lange, Jan 19 '16 at 20:20

Defining parameters in table valued function - SQL Server

2 Answers2