Using a User Defined Function while adding a column

I tried to figure out how to add the computation expression for a computed column using a user-defined function that I discovered while visiting an online forum.

Here’s an illustration.

— Create a table
CREATE TABLE dbo.Products (
ProductID int IDENTITY (1,1) NOT NULL ,
QtyAvailable SMALLINT,
UnitPrice MONEY);

— Data insertion
INSERT INTO dbo.Products (QtyAvailable, UnitPrice) VALUES (10,128),(11,29),(21,40);

— User-defined function for computation.
CREATE FUNCTION udfComputedColumn
(
@ipvParam1 INT, @ipvParam2 INT
)
RETURNS INT
BEGIN
RETURN @ipvParam1*@ipvParam2
END;

— Altering the table to add a computed column using UDF.
ALTER TABLE dbo.Products ADD InventoryValue AS dbo.udfComputedColumn(QtyAvailable,UnitPrice);

SELECT * FROM dbo.Products;

SQLServer_AUTO_UPDATE1

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 )

Connecting to %s