Odtnhj

Wrote a great simple function in SQL that apparently is not usable (or advisable) in my SELECT statement.

Have some intelligence behind combining Combinations of Company Name and Contact Name in our select and I find it's repeating across several views. Being a programmer, of course the right thing is to encapsulate that functionality for reuse across all views I'm created. But alas, from my searching it does not appear possible or recommended, at least not with UDFs.

The question: Is there any way to select the return value of a method/function/chunk of reusable code where I pass it the value of columns for each row... Or do I truly have to copy/paste the logic into each select statement?

SELECT formatName(company, contact, ' - ') as Name FROM company join contacts...

I know I can do this on the client (eventually), but client changes are not in scope for this phase of the project.

I guess I typed more in this question than just cutting and pasting a CASE statement into each view, but reuse is ingrained if me of course. :)

A better performing and DRY method to accomplish this is with a computed column.

A computed column is a virtual column that is not physically stored in
the table, unless the column is marked PERSISTED. A computed column
expression can use data from other columns to calculate a value for
the column to which it belongs. You can specify an expression for a
computed column in SQL Server 2017 by using SQL Server Management
Studio or Transact-SQL.

You can make this column persisted as well

PERSISTED Specifies that the Database Engine will physically store the
computed values in the table, and update the values when any other
columns on which the computed column depends are updated. Marking a
computed column as PERSISTED allows an index to be created on a
computed column that is deterministic, but not precise. For more
information, see Indexes on Computed Columns. Any computed columns
used as partitioning columns of a partitioned table must be explicitly
marked PERSISTED. computed_column_expression must be deterministic
when PERSISTED is specified.

alter table company add FullName as (FirstName + '-' + LastName) persisted;

Then, you could just add this column in your SELECT can can even query against it, if it's persisted.

What you can do is create a view that behaves like a table. Meaning it would have the performance of a table, can have indexes added etc. This view can have any of the columns of the underlying base table plus you can add calculated columns, such as [name]. This is accomplished by adding WITH SCHEMABINDING when creating the view. This view can then be used in lieu of the base table in all of your queries.

Here is an example.
The underlying base table with data:

CREATE TABLE dbo.company (
 companyid int IDENTITY(1,1) NOT NULL,
 company varchar(50) NULL,
 contact varchar(50) NULL,
 CONSTRAINT PK_company PRIMARY KEY CLUSTERED (companyid ASC) 
) ON FG1

The view containing WITH SCHEMABINDING:

CREATE view dbo.VW_company WITH SCHEMABINDING AS 
SELECT companyid, 
CASE WHEN RTRIM(ISNULL(company,'')) <> '' AND RTRIM(ISNULL(contact,'')) <> '' THEN company +' - '+ contact
 WHEN RTRIM(ISNULL(company,'')) <> '' THEN company
 WHEN RTRIM(ISNULL(contact,'')) <> '' THEN contact
 ELSE '' END as [Name]
FROM dbo.company 

This view can now be used everywhere the table is used, without a performance hit. Furthermore, the calculated column [Name] can actually have an index added to it! That's something you cannot do with a function.