question

Kurt 1 avatar image
Kurt 1 asked

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 characters needed characters left characters exceeded

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

Squirrel 1 avatar image
Squirrel 1 answered

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 characters needed characters left characters exceeded

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

Piotr Rodak avatar image
Piotr Rodak answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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