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');