Scalar 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)

Scalar Function

Author

Topic

jwmayo
Starting Member

28 Posts

Posted - 2010-08-18 : 11:42:53

Wondering if this is possible. I have sales table with product and customer information in it..

Sales File:
Customer#, PROD#

I want to create a function that based on the
customer# and prod# in the sales file it looks up the Discount in a discount table that contains:

Discount File:
Customer#, Prod#, Discount

Results:
Sales file:
Customer#, prod#, Discount

And again I need to be able to do this in a function.
Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 11:57:26

This can be achieved with select statement directly.

Select S.Customer, S.Prod, D.Discount from
Sales S inner join --or Use left join for getting all records from Sales table
Discount D on S.Customer = D.Customer and S.Prod = D.Prod

If any other columns also needs to be considered for discount then post the complete DDL statement.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-18 : 12:01:12

agreed it can be in just a select statement.. But need it in a function.. long story

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 12:11:50

Try this:

Create function GetDiscount(@Customer int, @PROD int)
Returns int
as
Begin
Declare @DiscAmt int
--Assuming only 1 discount exist for customer and product combination
Select @DiscAmt = D.Discount
from
Discount D where d.Customer = @Customer and D.Prod = @PROD

Return @DiscAmt

End

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-18 : 12:13:14

Thank you

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 12:19:06

quote:
Originally posted by jwmayo

Thank you

You are welcome.

I am here to learn from Masters and help new bees in learning.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-18 : 12:32:19

Column is populated with 0's.. this is what I have.. Any thoughts

USE [Dailysales]
GO
/****** Object: UserDefinedFunction [dbo].[discountSO] Script Date: 08/18/2010 12:22:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[discountSO] (
@newmarket varchar(25)
, @company varchar(25))

RETURNS int
AS
BEGIN
declare @discAmt int

select @discAmt = Discounttable.S
from
Discounttable where Discounttable.[new market] = @newmarket
and discounttable.company = @company

Return @discAmt
END

jwmayo
Starting Member

28 Posts

Posted - 2010-08-18 : 12:40:57

I got it.. Wrong data type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources