Getting started with SQL SELECT JOIN Example Databases and Tables UPDATE Indexes CREATE TABLE UNION / UNION ALL ALTER TABLE CASE INSERT Primary Keys ORDER BY GROUP BY Filter results using WHERE and HAVING Window Functions Common Table Expressions Views LIKE operator Functions (Aggregate)DELETE String Functions Data Types AND & OR Operators Triggers TRUNCATE MERGE Foreign Keys Finding Duplicates on a Column Subset with Detail Sequence Comments Subqueries Execution blocks Stored Procedures DROP Table Row number Transactions CREATE FUNCTION SQL Group By vs Distinct Table Design cross apply, outer apply Synonyms CREATE Database SKIP TAKE (Pagination)EXPLAIN and DESCRIBE Information Schema IN clause NULL SQL Injection Cascading Delete Order of Execution DROP or DELETE Database EXCEPT TRY/CATCH XML GRANT and REVOKE Functions (Scalar/Single Row)Relational Algebra EXISTS CLAUSE Materialized Views Functions (Analytic)SQL CURSOR Identifier Clean Code in SQL

CREATE FUNCTION

Remarks:

CREATE FUNCTION creates a user-defined function that can be used when doing a SELECT, INSERT, UPDATE, or DELETE query. The functions can be created to return a single variable or a single table.

Create a new Function

CREATE FUNCTION FirstWord (@input varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
    DECLARE @output varchar(1000)
    SET @output = SUBSTRING(@input, 0, CASE CHARINDEX(' ', @input)
        WHEN 0 THEN LEN(@input) + 1
        ELSE CHARINDEX(' ', @input)
    END)

    RETURN @output
END

This example creates a function named FirstWord, that accepts a varchar parameter and returns another varchar value.

Syntax:

CREATE FUNCTION function_name ( [list_of_paramenters] ) RETURNS return_data_type AS BEGIN function_body RETURN scalar_expression END

Parameters:

Argument	Description
function_name	the name of function
list_of_paramenters	parameters that function accepts
return_data_type	type that function returs. Some SQL data type
function_body	the code of function
scalar_expression	scalar value returned by function

Contributors

Topic Id: 2437

Example Ids: 8036

This site is not affiliated with any of the contributors.