CODEWITHSUNDEEP
sqlfunctionsql-server-2012
user3314502
user3314502

Reputation: 21

SQL Server : sum by cursor

This SQL Server 2012 function, and I want it to return all values by the cursor, but this SUM only last record from base.

When I want to SUM by: 

SET @AllSalary = @salarya + @AllSalary

it shows NULL.

I don't know what is the problem, it could be syntax error, fact is it doesn't display the desired result.

(param @montha INT, will by used later)

CREATE FUNCTION allCasR
(
    @montha INT
)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @AllSalary INT;
    DECLARE @salarya FLOAT;
    DECLARE @tymC FLOAT;
    -- Add the T-SQL statements to compute the return value here

DECLARE kursor_pensja CURSOR FOR
    SELECT contracts.salary
FROM dbo.contracts ;
OPEN kursor_pensja;
FETCH NEXT FROM kursor_pensja INTO @salarya
WHILE @@FETCH_STATUS=0 
BEGIN
    SET @AllSalary =+ @salarya
FETCH NEXT FROM kursor_pensja INTO @salarya
END
CLOSE kursor_pensja  
DEALLOCATE kursor_pensja
RETURN @AllSalary;
END

Upvotes: 1

Views: 7324

Answers (2)

marc_s
marc_s

Reputation: 754993

WHY on earth would you want to use a cursor for this??

Just use a SUM and since you're seeing NULL values, use ISNULL to convert NULL values into 0 (zero):

CREATE FUNCTION allCasR (@montha INT)
RETURNS INT
AS
BEGIN
    DECLARE @AllSalary INT;

    SELECT @AllSalary = SUM(ISNULL(salary, 0))
    FROM  dbo.Contracts 

    RETURN @AllSalary;
END

Update: if you must use a cursor as an exercise, then you need to make sure

  1. that you properly initialize your value for @AllSalary to 0 (otherwise it's NULL from the beginning and will never get any other value!)

  2. that you take care of the NULL values in the database table while iterating over the rows that would cause your entire SUM to be NULL in the end (either by excluding those values from the cursor with a WHERE salary IS NOT NULL or by applying a ISNULL(....) to the value being summed up):

Code should be:

CREATE FUNCTION allCasR (@montha INT)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @AllSalary INT;
    DECLARE @salarya FLOAT;
    DECLARE @tymC FLOAT;

    -- Add the T-SQL statements to compute the return value here
    DECLARE kursor_pensja CURSOR FOR
        SELECT contracts.salary
        FROM dbo.contracts ;

    -- you need to INITIALIZE this value to 0 !!!!! 
    SET @AllSalary = 0;

    OPEN kursor_pensja;
    FETCH NEXT FROM kursor_pensja INTO @salarya

    WHILE @@FETCH_STATUS=0 
    BEGIN
        -- you need to make sure to use ISNULL(.., 0) to avoid a NULL value in the SUM
        SET @AllSalary += ISNULL(@salarya, 0);

        FETCH NEXT FROM kursor_pensja INTO @salarya
    END

    CLOSE kursor_pensja  
    DEALLOCATE kursor_pensja

    RETURN @AllSalary;
END

Upvotes: 2

plalx
plalx

Reputation: 43728

SET @AllSalary =+ @salarya

Your + sign is after the =, that's why it doesn't work as expected.

It should be:

SET @AllSalary += @salarya;

EDIT:

"That return NULL"

If it returns NULL that means some of your values are NULL.

Use SELECT ISNULL(contracts.salary, 0) rather than SELECT contracts.salary.

Upvotes: 0

Related Questions