user Defined Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

user Defined Function

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2010-04-25 : 16:00:47

Hi

i am trying to make a UDF, that gives a discount. The Discount is dependent from the date.
The input is a number (price that needs the discount) and an ID.
The ID refers to a table, that has the date.
Until now i got following:


if exists 
(select	* from sysobjects
where name = 'FNdiscount' and type = 'FN')
drop function FNdiscount
go
CREATE FUNCTION FNdiscount(@SalesPrice nvarchar(250), @id nvarchar(250))
returns nvarchar(250)
AS
BEGIN
declare @datum nvarchar(255)
set @Date = "need to get it from a table"
if @Date < 1980 set @SalesPrice = @SalesPrice - @SalesPrice * 20%

return @SalesPrice 
END

i need to get the data from the table into the @date variable, and i need to return the salesprice, but i keep getting an error :/

Ty for the help ^^

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-25 : 17:19:12

>>
i need to get the data from the table into the @date variable,
<<
Use assignment SELECT like this:

SELECT @Date = Date
FROM table_name
WHERE id = @id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 00:43:30

[code]if exists
(select * from sysobjects
where name = 'FNdiscount' and type = 'FN')
drop function FNdiscount
go
CREATE FUNCTION FNdiscount(@SalesPrice nvarchar(250), @id nvarchar(250))
returns nvarchar(250)
AS
BEGIN

select @SalesPrice = case when Date < 1980
then @SalesPrice - (@SalesPrice * 20/100)
else @SalesPrice
end
from yourtable
where id = @id

return @SalesPrice
END

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2010-04-27 : 02:46:23

Worked ^^ Ty guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 03:51:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources