UDF Function with 2 parameters in tsql

Question

question

Kurt 1 asked May 28, '10

UDF Function with 2 parameters in tsql

i need to create a function that gets 2 parameters an id of a customer and a price i have to calculate the age of the customer and depending on his age i have to calculate a discount on the price

so for the function i tried

CREATE FUNCTION DISCOUNT(@ID INT,@PRICE INT) RETURNS TABLE RETURN SELECT BIRTHDATE FROM CUSTOMER WHERE BIRTHDATE > '10-14-1955' SET @price = @price - 10

The problem is that i need to return a scalar value instead of a table but i don't know how to access the birthdate colomn within a scalar function

is there someone who can help me? thanks

user-defined-function

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-28T09:12:22Z

Squirrel 1 answered May 28, '10

scalar function can only return 1 value. You want to return the birthdate and the price ?

Since both are not related, can you use 2 scalar function to do it ?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-05-28T15:08:21Z

Piotr Rodak answered May 28, '10

Something like this?

CREATE FUNCTION DISCOUNT(@ID INT,@PRICE INT)             
RETURNS TABLE             
RETURN             
SELECT BIRTHDATE, @price - 10 as discountedPrice            
FROM CUSTOMER             
WHERE BIRTHDATE > '10-14-1955'

But what @ID has to do with this rowset?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question