CODEWITHSUNDEEP
sqlsql-serverfunction
hc91
hc91

Reputation: 773

SQL Create Function

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.

Upvotes: 0

Views: 213

Answers (1)

John Bell
John Bell

Reputation: 2350

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.

Upvotes: 7

Related Questions