T-SQL – Procedures and functions

Procedures – are reusable pieces of code which perform an action and don’t return values.

Examples of creation and usage:

CREATE PROCEDURE Production.MakeBiggerPrice --creation
	@percent AS INT
AS
BEGIN
	UPDATE Production.Products SET unitprice = unitprice + unitprice * @percent / 100;
END;

EXECUTE Production.MakeBiggerPrice @percent = 5; -- usage A
EXECUTE Production.MakeBiggerPrice 5; -- usage B

Procedure with default and output parameter and turned off rows counter:

CREATE PROCEDURE Production.MakeBiggerPrice2 
	@percent AS INT,
	@categoryId AS INT = NULL, --default parameter
	@maxPrice AS MONEY OUTPUT --output parameter
AS
BEGIN
        IF @percent <= 0
                RETURN;
	SET NOCOUNT ON; --turns off counting edited rows (for performance optimisation)
	IF @categoryId IS NOT NULL
		UPDATE Production.Products SET unitprice = unitprice + unitprice * @percent / 100 
                WHERE @categoryId = @categoryId;
	ELSE
		UPDATE Production.Products SET unitprice = unitprice + unitprice * @percent / 100;
	
	SET @maxPrice = (SELECT MAX(unitprice) FROM Production.Products); --setting output parameter
	SET NOCOUNT OFF;
END;

DECLARE @max AS MONEY; --usage A
EXECUTE Production.MakeBiggerPrice2 @percent = 5, @maxPrice = @max OUTPUT;
PRINT 'Max price is: ' + CAST(@max AS VARCHAR(50));

DECLARE @max AS MONEY; --usage B
EXECUTE Production.MakeBiggerPrice2 @percent = 5, @categoryId = 1, @maxPrice = @max OUTPUT;
PRINT 'Max price is: ' + CAST(@max AS VARCHAR(50));

Dropping procedure:

DROP PROCEDURE Production.MakeBiggerPrice; -- simple way

IF OBJECT_ID('Production.MakeBiggerPrice2', 'P') IS NOT NULL -- with check
	DROP PROCEDURE Production.MakeBiggerPrice2;

Functions – are reusable pieces of code which perform an action to return values. They can return scalar values or tables. They cannot execute DDL commands and dynamic SQL, access temporary tables, use stored procedures.

Scalar user-defined function.

CREATE FUNCTION Production.GetMinPrice -- creation
(
	@categoryId AS INT
)
RETURNS MONEY -- returns scalar value
AS
BEGIN
	RETURN (SELECT MIN(unitprice) FROM Production.Products WHERE categoryid = @categoryId);
END

SELECT Production.GetMinPrice(1); --usage (SELECT, not EXEC)

Table-valued user-defined function.

CREATE FUNCTION Production.GetCheapProducts -- creation
(
	@categoryId AS INT
)
RETURNS @result TABLE -- returns defined table
(
  [id] INT,
  [price] MONEY
)
AS
BEGIN
	INSERT @result
	SELECT productId, unitprice FROM Production.Products 
        WHERE categoryid = @categoryId AND unitprice < (SELECT AVG(unitprice) FROM Production.Products);
	RETURN;
END

SELECT * FROM Production.GetCheapProducts(1); -- usage (select like from normal table)

Simplier table-valued function (no returned table declaration).

CREATE FUNCTION Production.GetCheapProducts2 -- creation
(
	@categoryId AS INT
)
RETURNS TABLE AS RETURN -- returs table
(
	SELECT productId, unitprice FROM Production.Products 
        WHERE categoryid = @categoryId AND unitprice < (SELECT AVG(unitprice) FROM Production.Products)
)

SELECT * FROM Production.GetCheapProducts2(1); -- usage (select like from normal table)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s