My UDF won't work when I try to use it

Question

I am still kind of a beginner here but I created this UDF:

USE NorthWind
GO
IF EXISTS(SELECT name FROM SYSOBJECTS
                 WHERE name='SaleAfterDiscount'
                 AND type ='FN')
BEGIN 
DROP FUNCTION SaleAfterDiscount;
END
GO
CREATE FUNCTION SaleAfterDiscount(@pPrice AS MONEY, @pQty AS SMALLINT, @pDiscount AS REAL)
RETURNS MONEY AS 
BEGIN
DECLARE @SaleAfterDiscount MONEY;
SET @SaleAfterDiscount=(@pPrice*@pQty*(1-@pDiscount));
RETURN @SaleAfterDiscount;
END;

Then I go to use this UDF to get a value and I get this error that says "The Multi-Part Identifier "dbo.SaleAfterDiscount" could not be bound. I am not sure what I did wrong. Can anyone help?

Your function is completely broken. Set with nothing....there is no return. Why send in three parameters to ignore them all? — Sean Lange, Mar 29 '18 at 20:13
And be careful using real here. You should use decimal instead because real is an approximate datatype. — Sean Lange, Mar 29 '18 at 20:14
Your `IF Exists` isn't complete. You need an ending parenthesis. Among other things as stated above. — SS_DBA, Mar 29 '18 at 20:14
I am trying to use it to get the value of a column from another table — Zachary, Mar 29 '18 at 20:21
That's not what I asked. What SQL are you using to call the function? Can you add that to your question please? — Thom A, Mar 29 '18 at 20:23
@Zachary, I didn't get any error in this function. you can check using this statement SELECT [dbo].[SaleAfterDiscount] (100,1,25) AS MONEY. — Emdad, Mar 29 '18 at 20:25
Now that you fixed all the syntax errors your code works fine. select dbo.SaleAfterDiscount(100, 2, .10) — Sean Lange, Mar 29 '18 at 20:41
Oh ok I didn’t know I had to put three values in the paranthyses after the UDF. I guess that made too much sense. Sorry for being such a noob — Zachary, Mar 29 '18 at 20:49

score 1 · Answer 1 · answered Mar 29 '18 at 20:45

1

Since the CREATE FUNCTION does not specify a schema, it is likely it being created in a different schema (depends on your user config).

Try changing the CREATE to look like below

CREATE FUNCTION dbo. SaleAfterDiscount

answered Mar 29 '18 at 20:45

RnP

390
1
8

My UDF won't work when I try to use it

1 Answers1