I have this function I am trying to create. When I parse it, it works fine, but to actually create the function in the database it says my column names are invalid. That is not true, I spelled them correctly. Here is the code:
ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @Final nvarchar
IF EXISTS (
SELECT
question,
yes_ans,
no_ans,
na_ans,
blank_ans
FROM dbo.qrc_maintally
WHERE school_id = @SchoolId
)
IF yes_ans > no_ans AND yes_ans > na_ans
BEGIN
SET @Final = 'Yes'
END
ELSE IF no_ans > yes_ans AND no_ans > na_ans
BEGIN
SET @Final = 'No'
END
ELSE IF na_ans > yes_ans AND na_ans > no_ans
BEGIN
SET @Final = 'N/A'
END
RETURN @Final
END
In MS SQL, IF…ELSE is a type of Conditional statement. Any T-SQL statement can be executed conditionally using IF… ELSE. If the condition evaluates to True, then T-SQL statements followed by IF condition in SQL server will be executed.
IF condition in SQLIF() function is passed with two parameters, one for true and other for false. The function returns one value if a condition is TRUE, and another value if the condition is FALSE.
The IF ELSE statement Each IF statement has a condition. If the condition evaluates to TRUE then the statement block in the IF clause is executed. If the condition is FALSE , then the code block in the ELSE clause is executed. Finally, the IF clause checks if the sales amount in 2017 is greater than 10 million.
ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS nvarchar(3)
AS BEGIN
DECLARE @Final nvarchar(3)
SELECT @Final = CASE
WHEN yes_ans > no_ans AND yes_ans > na_ans THEN 'Yes'
WHEN no_ans > yes_ans AND no_ans > na_ans THEN 'No'
WHEN na_ans > yes_ans AND na_ans > no_ans THEN 'N/A' END
FROM dbo.qrc_maintally
WHERE school_id = @SchoolId
Return @Final
End
As you can see, this simplifies the code a lot. It also makes other errors in your code more obvious: you're returning an nvarchar, but declared the function to return an int (corrected in the code above).
You'll need to create local variables for those columns, assign them during the select and use them for your conditional tests.
declare @yes_ans int,
@no_ans int,
@na_ans int
SELECT @yes_ans = yes_ans, @no_ans = no_ans, @na_ans = na_ans
from dbo.qrc_maintally
where school_id = @SchoolId
If @yes_ans > @no_ans and @yes_ans > @na_ans
begin
Set @Final = 'Yes'
end
-- etc.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With