# How to Create Scalar Value Function in SQL

In this article I am going to explain how to create scalar value function in SQL.
• 3125

Introduction

In SQL Server there are two type of function which are used to manipulate data in database.

• System define function
• User define function

SQL server provide a list of system define function which are used to manipulate data. These system define function are predefine. We can't change functionality of these system define function. There are many situation where we need a function which performer some specific task according to our need. There function are known as user define function.

There are three types of user define function in SQL Server.

• Scalar- value function
• Simple table-valued function
• Multi-statement table-valued function

Scalar- value function

Scalar- value function is used to return a single value of any T-SQL data type.

A Statement that is used to create a mcninvoices table

 create table mcninvoices ( invoiceid int not null identity primary key, vendorid int not null, invoiceno varchar(15), invoicetotal money, paymenttotal money, creadittotal money )

A Statement that is used to insert data in mcninvoices table

 insert into mcninvoices values (20,'e001',100,100,0.00) insert into mcninvoices values (21,'e002',200,200,0.00) insert into mcninvoices values (22,'e003',500,0.00,100) insert into mcninvoices values (23,'e004',1000,100,100) insert into mcninvoices values (24,'e005',1200,200,500)

A Statement that show data in mcninvoices table A Statement that create scalar value function

 CREATE FUNCTION fn_bd()       RETURNS MONEY BEGIN       RETURN ( SELECT SUM(invoicetotal-paymenttotal-creadittotal)       FROM dbo.mcninvoices       WHERE invoicetotal-paymenttotal-creadittotal >0 ) END

A Statement that uses scalar value function
{"ft_lang_label":"__label__en","ft_lang_prob":0.75907785,"math_prob":0.92418754,"size":1787,"snap":"2022-27-2022-33","text_gpt3_token_len":430,"char_repetition_ratio":0.1912507,"word_repetition_ratio":0.03137255,"special_character_ratio":0.23670956,"punctuation_ratio":0.11912226,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.951778,"pos_list":[0,1,2,3,4,5,6],"im_url_duplicate_count":[null,null,null,3,null,3,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-07-01T05:28:54Z\",\"WARC-Record-ID\":\"<urn:uuid:d499105e-5aa5-440e-8b6d-ba156802911a>\",\"Content-Length\":\"64634\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:c469f7f8-2bd1-4650-b594-2d2e3583e8bc>\",\"WARC-Concurrent-To\":\"<urn:uuid:1a7e868c-5b76-45c0-a3f3-3f600fca6f25>\",\"WARC-IP-Address\":\"172.67.145.26\",\"WARC-Target-URI\":\"https://www.dotnetheaven.com/article/how-to-create-scalar-value-function-in-sql\",\"WARC-Payload-Digest\":\"sha1:B2PHC6Q43IHEKAPY5KORGN2OX6R2QS5G\",\"WARC-Block-Digest\":\"sha1:MCKBTBYOD5CSAEPH4PH2V3SERO3ROFTH\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-27/CC-MAIN-2022-27_segments_1656103920118.49_warc_CC-MAIN-20220701034437-20220701064437-00153.warc.gz\"}"}