Use functions and procedures to make your SQL Server life convenient

A nice to have feature when working with databases is the different ways to create functions and procedures to create reusable queries. I don’t say queries used with applications, because you in the database layer for applications you should avoid putting too much logic and filtering in the database and instead add it to the data layer in your application. At least my opinion, and possible something that is revised depending on situation.

But in case you do some work in the database, and want the possibility to reuse common queries there are some good support for it in SQL Server: Scalar Functions, Table Functions and Stored Procedures.

Scalar Function is used to act upon a column value, e.g. in the SELECT clause part of your SQL query. Or just about any place where you think you might need to do something with column value.

CREATE FUNCTION sf_formatdate(
  @date DATETIME2
)
RETURN VARCHAR(100) AS
BEGIN
  -- You can do any queries here
  RETURN CONVERT(DATETIME2, @date, 108)
END
GO

SELECT sf_fortmatdate(TRANSACTIONDATE) FROM SomeTable

Table Function is used when you want to simplify part of a SELECT or JOIN. It actually returns a new table with the query, so it can be selected or joined from.

CREATE FUNCTION tf_fetchsomething (
  @id INT
)
RETURN TABLE AS
BEGIN
  -- You can do any queries here
  SELECT A.ID A_ID, B.ID B_ID, C.ID C_ID, C.VALUE C_VALUE, D.VALUE D_VALUE FROM A JOIN B ON A.ID = B.ID OUTER JOIN C WHERE C.ID = B.ID WHERE C.VALUE > D.VALUE AND A.VALUE = @id
END
GO

SELECT * FROM tf_fetchsomething (100) F JOIN E ON F.A_ID = E.ID

Stored Procedure you can use when you just want run a query without expecting to reuse the result in another query, not expecting a result to be returned. Simplify an insert or just fetch data using your scalar and table functions to get a result set of some kind.

CREATE PROCEDURE sp_printdata (
  @id INT = 100 -- Default to 100 if no parameter is given
)
AS
BEGIN
  SELECT sf_formatdate(E.DATE) DATE, F.* FROM tf_fetchsomething(@id) F JOIN E ON F.A_ID = E.ID
END

So you can obviously read and learn more about the different functions, I just wanted to show you the possibilities you have to organize queries for reuse. Table Functions can be used to just simplify complex queries, or queries where you reach into tables that not that many in your team (or you) usually look in, return as many columns as you like . It’s a convenient feature. Scalar functions can be used when you need to format data for output in a common way across queries you usually do in your database. And stored procedures can be used to make more specified convenient methods where you might want a simple input and just showing the columns that are of interest.