stored procedure vs UDF

Question

I have a select statement and in a couple of the fields, I want to check if an entry for the record exists in another table and if it does, output 1 value and if it doesn't, provide another value.

What would be the best way to do it? When would you use a stored procedure and when would you use a UDF?

Answer 1

The nice thing about a UDF is that you can use them inside other queries. For example, you can say:

SELECT my_UDF( t.id ) FROM my_table t

You can also use a UDF like a table:

SELECT * FROM my_udf() t

So if you think that your query contains some general-purpose logic that may be used in several queries across your application / database, you may want to consider using a UDF. But there are some limitations. For example, a UDF cannot call non-deterministic built-in functions such as GETDATE. So you would need to use a stored procedure to work with these functions. For more information, see: difference-between-user-defined-function-and-stored-procedure.

Answer 2

I would use a Left Join.

Select Coalesce(OtherTable.Field, 'Default Value') As Value
From Table
    Left Join OtherTable On Table.OtherTableId = OtherTable.Id
Where Table.Id = @Id

Answer 3

Typically I will use views where possible to encapsulate low-level logic and conforming. If a parameterized view (i.e. an inline table-valued function) will allow you to enforce certain parameters being supplied, I would use that. If a multi-statement action needs to be performed, at that point I would use a stored procedure.

The benefits of views and inline table valued functions is that the optimizer can manipulate them a lot more easily (views can be combined in building the execution plan), and views and ITVFs can be used just like tables and joined to other tables, views or ITVFs with effectively little or no overhead compared to repeating the code inline.

For instance, the code in ChaosPandion's answer can be encapsulated in an ITVF taking a parameter (a great way to ensure that certain parameters are ALWAYS supplied - there is no way in a view to ensure that filter criteria are going to be supplied in the outer SELECT) or in a view (and the parameter will need to be supplied outside the view). The execution plan in both cases will actually be equivalent, because the optimizer can handle cases like this very well.

Stored procedure result sets cannot be joined without putting the data into a temporary table first.

CREATE VIEW TableWithDefault
AS
Select Table.Id, Coalesce(OtherTable.Field, 'Default Value') As Value
From Table
    Left Join OtherTable On Table.OtherTableId = OtherTable.Id

-- Usage: SELECT * FROM TableWithDefault WHERE Id = @Id

CREATE FUNCTION TableWithDefault(@Id int)
RETURNS TABLE
RETURN (
    Select Coalesce(OtherTable.Field, 'Default Value') As Value
    From Table
        Left Join OtherTable On Table.OtherTableId = OtherTable.Id
    Where Table.Id = @Id
)

-- Usage: SELECT * FROM TableWithDefault(@Id)

Now the benefits of this is that this logic can be called from various stored procedures, views (OUTER APPLY, CROSS APPLY) etc. and the logic is always in one place.