SQL Create Function

Question

I'm having trouble calling a function that I have created. Any help would be most appreciated. Here is the function:

     CREATE FUNCTION myFunction
(
@T DECIMAL(38,14),
@R DECIMAL (38,14),
@S DECIMAL(38,14)
)
RETURNS DECIMAL(38,14)
AS
BEGIN

RETURN 

SQRT(
    ABS(
            (@T/(@R-1))-((@R/(@R-1))*(@S*@S))))
/@S

END
GO

I then need to use this function on a table. This is to fill the 'CV' column. This needs to work on the following columns:

@T Decimal (38,14) as SumSqJT
@R Decimal (38,14) as "SumObs"
@S Decimal (38,14) as "AvJT"

EDIT:

This is the function I've then used

Use [Neutral Month 20132014 ALL]

SELECT 
     dbo.MyFunction(SumSqJT1, SumObs, AvJT) AS FunctionResult
FROM
     Essex_Data_1314
WHERE
     SumObs > 1

This creates the list of figures, however I need to update column CV in table Essex_Data_1314 not just create a list of figures. How do i do that?

This has now been solved to be this:

Use [Neutral Month 20132014 ALL]

UPDATE
    Essex_Data_1314
SET
    CV = dbo.MyFunction(SumSqJT1, SumObs1, AvJT)

Note: It is important to have everything as either a DECIMAL or a NUMERIC datatype. Otherwise conversion errors occur.

Thanks for the help JohnnyBell.

Cheers.

Just to make it clear. Your function is created without any errors, but you do not know how to call it? — Guneli
– Guneli, Commented Apr 16, 2015 at 8:41
how did you try to call it? did you try to search on google/bing/yahoo/ msdn/ stackoverflow ? — ughai
– ughai, Commented Apr 16, 2015 at 8:44

John Bell · Accepted Answer · 2015-04-16 09:15:54Z

7

I'm not really sure what the problem is. Or if there is any problem. If you want to know how to call your created function, you'll need to do something like this:

SELECT MyFunction(@T,@R,@S)

Obviously replacing the @T, @R, @S with the values you like to pass through the function.

If you want to use the function on columns within a table, you can reference the column names inside the function call like so:

SELECT 
    MyFunction(Column1, Column2, Column3) AS FunctionResult
FROM
    MyTable

If you want to update a column with these results, you can easily do this with an UPDATE statement:

UPDATE
    MyTable
SET
    FunctionResult = MyFunction(Column1,Column2,Column3)

Thanks.

edited Apr 16, 2015 at 9:15

answered Apr 16, 2015 at 8:41

John Bell

2,3501 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hc91 Over a year ago

I need to use the function on a whole table, not just a few values. Is that possible?

hc91 Over a year ago

How do I use this function to update a column and not just create a list of figures? Thanks.

John Bell Over a year ago

I'll edit my answer again, check in a few minutes :)

hc91 Over a year ago

i've fixed all the conversion errors and now have created the same answers as in Excel :). Thanks fo ryour help.

Collectives™ on Stack Overflow

SQL Create Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related