{"id":6657,"date":"2022-03-07T06:30:00","date_gmt":"2022-03-07T05:30:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=6657"},"modified":"2023-03-23T07:39:30","modified_gmt":"2023-03-23T06:39:30","slug":"how-to-create-a-sql-server-function","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/how-to-create-a-sql-server-function\/","title":{"rendered":"How to create a SQL Server function with a script ?"},"content":{"rendered":"\n

Example of T-SQL code to create a simple SQL Server user defined function.<\/em><\/strong><\/h4>\n\n\n\n

Tutorial to create a simple SQL Server function with a T-SQL script. You can use the code provided as a syntax checklist or an example to copy and paste. In fact, the script creates a SQL function with a customer number as a parameter and the customer’s name as the result.<\/p>\n\n\n\n

T-SQL functions, like stored procedures, are at the heart of Microsoft database programming. They allow to manage the intelligence of the databases and to reuse the code with a single keyword. The basic principle is to reuse code instead of programming the same thing over and over again.<\/p>\n\n\n\n

1. Add a source table to use in the function<\/h2>\n\n\n\n

To start, create the customers table with the code available on this page with the code for creating the Customers example table. The code allows you to have the source table used by the view. The source table in your case will be adapted with your own objects. This type of function is a user defined<\/a> function. The name is stored in the return variable @CustomerName. Use the sample table creation code available here : <\/p>\n\n\n\n

-- If the Customers table already exists, then we drop it\nIF exists(   \n     SELECT  1 FROM sys.objects\n     WHERE   object_id = object_id(N'[dbo].[CUSTOMERS]') AND type in (N'U')\n)\nBEGIN\n     DROP TABLE [dbo].[CUSTOMERS]\nEND\nGO\n\n-- We Create the CUSTOMERS table with the Column [Name] declared as Unique\n-- The UNIQUE keyword don't allow to have two different customers with the same name\nCREATE TABLE [dbo].[CUSTOMERS] (\n     [CustomerNumber]       INTEGER IDENTITY(1,1),\n     [Name]      NVARCHAR(20) UNIQUE,\n     [City]      NVARCHAR(20)\n)\nGO\n\n-- Data insertion for the function example\nINSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'John', 'New York');\nINSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Ahmed', 'Los Angeles');\nINSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Jane', 'Miami');\nINSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Lee', 'Chicago');\nINSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Diana', 'San Francisco');\n\n-- Let's verify the inserted lines for our 5 customers\nSELECT * \nFROM dbo.CUSTOMERS;\n<\/pre>\n\n\n\n
This sample function in the next section accepts as input the customer number and returns the customer name.<\/p>\n\n\n\n
2. Create the function using a T-SQL script<\/h2>\n\n\n\n
The 6 important steps in the SQL Server function declaration below are the following.\u00a0<\/p>\n\n\n\n
    \n
  1. We define the entry parameter as an Integer, i.e. @CustomerNumber<\/em>.<\/li>\n\n\n\n
  2. We declare the return type, here it is the NVARCHAR(20) data type.<\/li>\n\n\n\n
  3. Then we declare the variable @CustomerName<\/em>, also with the NVARCHAR(20) data type.<\/li>\n\n\n\n
  4. Select the Customer name from the CUSTOMERS table using the @CustomerNumber<\/em> variable. Indeed, a customer as a unique customer number so we use it as the key for the selection.<\/li>\n\n\n\n
  5. In the SELECT query, the customer name is stored into the @CustomerName<\/em> variable.<\/li>\n\n\n\n
  6. Then we return customer name using the variable.<\/li>\n<\/ol>\n\n\n\n
    CREATE FUNCTION dbo.ufnDisplayCustomers(@CustomerNumber INTEGER)\nRETURNS NVARCHAR(20)\nAS \nBEGIN \n  DECLARE @CustomerName  AS NVARCHAR(20);\n  SELECT  @CustomerName = [Name]\n  FROM    [dbo].[CUSTOMERS]\n  WHERE   [CustomerNumber] = @CustomerNumber;\n  RETURN  @CustomerName;\nEND\nGO\n<\/pre>\n\n\n\n
    Once created, you can easily call and test the SQL Server function<\/a> using this script.<\/p>\n\n\n\n
    SELECT dbo.ufnDisplayCustomers(1);\n<\/pre>\n\n\n\n
    Note that the syntax for calling a function is the same as for a table. The difference is the passing of the parameter. For a function with no parameter then the parentheses remain empty. To go further, there are different types of SQL functions to study. This article shows how to create a SQL Server function, check here this article on how to create a text list of files available in one folder in cmd<\/a>.<\/p>\n\n\n\n
    Conclusion on creating a function with T-SQL<\/h2>\n\n\n\n
    In conclusion, learning how to create a simple SQL Server function with a T-SQL script is a fundamental skill for database developers and administrators. This tutorial demonstrated the process of creating a user-defined function that takes a customer number as a parameter and returns the corresponding customer’s name. By leveraging T-SQL functions, you can encapsulate complex logic and promote code reusability across all your database applications. Which one of the most important good practice in progamming.<\/p>\n\n\n\n
    \n
    Modify a SQL Server function with alter function<\/a><\/blockquote>