Exec a 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

General SQL Server Forums

New to SQL Server Programming

Exec a function

Author

Topic

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-11 : 16:30:54

Hi,

How to exec the following function from my select statement and get my return value:-

ALTER FUNCTION [dbo].[fn_qty]
(@qty decimal (10,4),
@price decimal (10,4),
@pieces int,
@mpt int=0)

returns decimal (10,4)

AS
begin
declare @totamt decimal (10,4)
if @pieces = 0
begin
SET @totamt = (@QTY * @price)
end
else
begin
SELECT @mpt = (SELECT case mpq
when 1 then 10
when 2 then 100
else 100
end
FROM ims.parm)
SET @totamt=(((FLOOR(@QTY)*@PRICE))+(((@qty-FLOOR(@QTY))* @mpt)/@pieces)*@price)
end
return @totamt
end

I can exec the function as exec command as follows:-
exec @totamt( erpinv.dbo.fn_qty 3.5,6,24 )

Best Regards

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-11 : 16:35:18

select @totamt = erpinv.dbo.fn_qty(3.5, 6, 24)

have you even looked in BOL?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 16:42:29

DECLARE @result decimal(10,4)
SET @result = dbo.fn_qty(3.5,6,24)

Jim

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-11 : 16:50:00

spirit1 thanks for your help, but doesn’t works. believe me this my first function in ms sql !!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-11 : 16:51:39

"doesn't work" doesn't help much

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 17:09:44

Start with if @pieces = 0 change to if @pieces >0, to avoid divide by zero errors
also , what is the result of this part
SELECT case mpq
when 1 then 10
when 2 then 100
else 100
end
FROM ims.parm

Jim

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-11 : 17:33:55

this is a part of sp for inventory system. this part case the number of qty decimal for calculte the total amount value.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 17:38:37

whaat is the result of running this by itself
SELECT case mpq
when 1 then 10
when 2 then 100
else 100
end
FROM ims.parm

in the query analyzer

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-11 : 17:41:09

10,100,1000

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 18:01:36

You need to make it so that only value is returned. mpq needs to be limited to a single value. Perhaps adding where clause to knwo which mpq to use?

jim

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-11 : 18:55:53

jimf the function is working fine with exec as the following:-

exec @totamt( erpinv.dbo.fn_qty 3.05,6,24 )

and I have a return value, but how to use it with select statement.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 19:59:07

We're back to square one. What error do you get when you try spirit1's answer or mine? Give us the select statement that you want to use it in. You should be able to do
SELECT col1,col2,erpinv.dbo.fn_qty 3.05,6,24 )
FROM
yourtable

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 02:13:29

Functions can't accept default values like stored procedures can!
You must explicit set DEFAULT as parameter value!

SET @result = dbo.fn_qty(3.5,6,24, default)
SET @result = dbo.fn_qty(3.5,6,24, 0)

E 12°55'05.25"
N 56°04'39.16"

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-12 : 02:23:30

Msg 313, Level 16, State 2, Procedure cardex, Line 25
An insufficient number of arguments were supplied for the procedure or function erpinv.dbo.fn_qty.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 09:21:56

Did you even LOOK at my suggestion posted 10 minutes earlier?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources