Today I will tell you how to write Stored Procedures and Functions in SQL Server Database.

Stored Procedure

SQL Server stored procedures are used to group one or more Transact-SQL statements into logical units.

CREATE PROCEDURE sp_Name
@Param BIGINT
AS
BEGIN
SELECT @Param
END

EXEC sp_Name 123;

Scalar-valued Function

A Scalar-valued function in SQL Server Is used to return a single value of any T-SQL data type

CREATE FUNCTION fn_Name(@Param1 BIGINT, @Param2 BIGINT)
RETURNS BIGINT
AS
BEGIN
RETURN @Param1 + @Param2
END

SELECT dbo.fn_Name(1, 1);

Table-valued Function

A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.

CREATE FUNCTION SPLIT_STRING(@String NVARCHAR(MAX))  
RETURNS @Temptable TABLE(ID NVARCHAR(512))  
AS  
BEGIN  
DECLARE @Delimiter CHAR(1);
SET @Delimiter = ',';
DECLARE @INDEX int, @SLICE VARCHAR(8000)  
SELECT @INDEX = 1  
IF LEN(@String) < 1 OR @String IS NULL  
return  
WHILE @INDEX != 0  
BEGIN  
SET @INDEX = CHARINDEX(@Delimiter, @String)  
IF @INDEX != 0  
BEGIN  
SET @SLICE = LEFT(@String, @INDEX - 1)  
END  
ELSE  
BEGIN  
SET @SLICE = @String  
END  
IF(LEN(@SLICE) > 0)  
BEGIN  
INSERT INTO @Temptable(ID) VALUES(@SLICE)  
END  
SET @String = RIGHT(@String, LEN(@String) - @INDEX)  
IF LEN(@String) = 0  
break  
END  
RETURN  
END 

SELECT * FROM dbo.SPLIT_STRING('mousa,alsheikh');