Today’s subject is about something very interesting on SQL Server, which is the creation of a calculated column using a function.
Yes, it is possible to create a column on a table based on a function and to do that we will follow these steps:
- Creation of a table (with columns: id and name)
- Insert data on this table
- Creation of a SQL function (that will return “id – name”)
- We will add a column using the created function
Attention! This is an example created to show you this functionality and the use of this example can impact your queries performance,
The use of this solution is your responsibility.
Let’s create the table with the following SQL command:
--drop TABLE dbo.TempSuzuki go CREATE TABLE dbo.TempSuzuki ( ID INT IDENTITY(1,1), Name VARCHAR(50) )
Let’s insert some data on this new table with the following SQL command:
INSERT INTO dbo.TempSuzuki VALUES ('Thiago Satoshi Suzuki') INSERT INTO dbo.TempSuzuki VALUES ('João da Silva')
Let’s query the create table to see if our commands worked with the following command:
SELECT * FROM dbo.TempSuzuki
You should get a result like this:
Let’s create our function with the following SQL command:
--DROP FUNCTION dbo.TestTempSuzuki GO CREATE FUNCTION dbo.TestTempSuzuki(@id INT) RETURNS VARCHAR(100) AS /* <SUMMARY CREATED="30/04/2015" CREATOR="SUZUKI" Used to create the date column. </SUMMARY> <HISTORY> </HISTORY> */ BEGIN DECLARE @result VARCHAR(100) SELECT @result = CAST(ID AS VARCHAR) + ' - ' + Name FROM dbo.TempSuzuki WHERE ID = @id RETURN @result END GO
The following SQL command will show how to use the created SQL function on a query:
select *, dbo.TestTempSuzuki (id) from dbo.TempSuzuki
The query’s result should be like this:
This is the “magic” SQL command that will add a column to our table and that will use the SQL function that we created before:
ALTER TABLE dbo.TempSuzuki ADD Composite AS dbo.TestTempSuzuki(id)
From now on when we query our table with the following command:
select * from dbo.TempSuzuki
We should have the following result:
Note that the Composite column is the SQL function’s result.
Thanks for reading.
Like and Share if you found it may be useful to someone you know!