Get a bit as a result of comparing two numbers

user_16678827 0
Viewed 187

I have a problem with bellow code. I am trying to insert into @ResultTable a third column that is a result of type bit of comparsion two numbers. If the val is bigger than avg(val) it should return 1 if not then 0. I know how to do it with Case but it should be possible to do it with just bit type right?

Create FUNCTION [dbo].[GetAvg](@col AvgRes READONLY)
RETURNS @ResultTable TABLE(id int, val float, result BIT)
AS 
BEGIN
    DECLARE @result bit
    DECLARE @avgval int
    SET @avgval = (select AVG(val) FROM @col)
    SET @result = val > @avgval <---- I have problem right there, I get Incorrect syntax near '>'. error

    Insert into @ResultTable
        SELECT id, val, @result
        FROM @col
RETURN
END
sql-server sql database
3 Answers

Further to what @GordonLinoff is saying, I strongly suggest you convert this to an inline Table-valued Function, which performs far better

CREATE OR ALTER FUNCTION [dbo].[GetAvg]
  (@col AvgRes READONLY)
RETURNS TABLE
AS RETURN
(
  SELECT
    c.id,
    c.val,
    result = CAST(CASE WHEN c.val > AVG(c.val) OVER () THEN 1 ELSE 0 END AS bit)
  FROM @col c
);

GO
user_148689970

SQL Server does not support boolean types -- and bit is not a boolean. It is a 1-bit integer.

So, you need to use a case expression:

SET @result = (CASE WHEN val > @avgval THEN 1 ELSE 0 END);

That said, there is no reason for such complexity. You can express the logic without variables or subqueries:

Insert into @ResultTable (id, val, result)
    SELECT id, val,
           (CASE WHEN val > AVG(val) OVER () THEN 1 ELSE 0 END)
    FROM @col;
user_11440350

Another solution is to use the built-in function IIF and return either a 1 or 0:

SET @result = IIF(val > @avgval, 1, 0);
user_60722240
Related