SQL Server, how to use a user function as default for a column

Hi guys,

Welcome this post mates.

Today a super light post only to show how to use a function as a default value for a column of a SQL server Table.

Simply and easy as usual!

Enjoy the reading!

 

How to use a function as default for a column

 

A columns of a table in SQL Server can have a default value.

Simply, if I insert a row using the insert statement and I do not specify the name of a field, in that case the default value will be used for that field.

Let's see how a default is defined on a field.

To add a default value you simply add the default and a value after the type definition of the field.

CREATE TABLE ORDTES
(ID INT IDENTITY(1,1),
 CODE VARCHAR(20),
 TOTAL_VALUE FLOAT DEFAULT 0
)

Obviously we can specify any value consistent with the type of the field.

Now, if you do not specift the field total_value in the insert statement:

INSERT INTO ORDTES (CODE) VALUES ('AAA')

The default value will be used:

Sometimes, however, our default is not a fixed value.

How can we do?

The idea is to use a function!

Microsoft puts this among its examples:

CREATE TABLE ORDTES
(ID INT IDENTITY(1,1),
 CODE VARCHAR(20),
 TOTAL_VALUE DATETIME GETDATE()
)

This means we can use functions, but the GetDate function is not a user defined function (UDF).
We need to make our own select ..

 

UDF and Default

In reality we can also pass as default value a function defined by us (UDF) ... paying attention to a small trick ..

Let's create our own UDF.

CREATE FUNCTION [DBO].GET_TOTAL()
RETURNS INTEGER
AS 
BEGIN
   DECLARE @RES INT;
   SET @RES = (SELECT MAX(R.VALUE) FROM ORDRIG R WHERE R.IDORDTES = T.ID) 
   RETURN @RES 
END

If now we try to use it as a default:

CREATE TABLE ORDTES
(ID INT IDENTITY(1,1),
 CODE VARCHAR(20),
 TOTAL_VALUE FLOAT DEFAULT GET_TOTAL()
 )

We get this error message:

Msg 195, Level 15, State 10, Line 4
'GET_TOTAL' is not a recognized built-in function name.

Reading the message one is led to think that you can only use the built-in functions, in reality it is sufficient to specify the owner.

CREATE TABLE ORDTES
(ID INT IDENTITY(1,1),
 CODE VARCHAR(20),
 TOTAL_VALUE FLOAT DEFAULT DBO.GET_TOTAL()
 )

That'all for today

Stay tuned for the next post and "NO TO THE WAR IN UKRAINE"

Follow me on linked (click follow) in you like this post

 

Luca

 

 

 

 

Previous post:  SQL Server Toolkit 5-1 for MSSQL, the review