0

I have this code that is part of a stored procedure:

SET @_Value = 0
SET @_Cont = 1;

IF(@_FlagControl = 1)
BEGIN
    SELECT @_Value = SUM(Value)
    FROM Person P 
    INNER JOIN Order O on O.CodPerson = P.CodOrder
    WHERE P.CodPerson = @_CodP
        AND P.CodImp <> 3
        AND P.FlagSituation = 1
        AND @_CodMainPerson = P.CodPerson

    IF(@_Value IS NULL)
        PRINT 'NULL'
    ELSE
        PRINT @_Value
END

If I run just this SELECT inside the "IF", it returns '0.90'. But when I run this entire query inside a procedure, it is printing NULL.

I don't have idea what is going on.

Mucida
  • 603
  • 9
  • 24
  • What if `@_FlagControl <> 1` and some other statement make @_Value as NULL – Pரதீப் Dec 12 '14 at 13:08
  • If I put PRINT @_Value just before the SELECt, it prints "0". and jsut after the SELECT it prints nothing. – Mucida Dec 12 '14 at 13:11
  • The results would depend on the values of `@_CodP` and `@_CodMainPerson`. It doesn't surprise me that sometimes it prints one value and sometimes another. – Gordon Linoff Dec 12 '14 at 13:11
  • But those are passed by parameters and don't change inside the procedure. I printed the values that are being used (@_CodP and @_CodMainPerson), and ran the same query in another tab with these values, it returns 0.90 – Mucida Dec 12 '14 at 13:14
  • 1
    SQL aggregate fucntions, except count, return null if no input rows go into the function. Maybe the conditions in your where clause result in an empty set? – Mithrandir Dec 12 '14 at 13:15
  • @Mithrandir, but I printed those @_CodP and @_CodMainPerson values, and used them to run this SELECT in another tab, and it returns 0.9 – Mucida Dec 12 '14 at 13:17
  • then try hard coding `@_CodP` and `@_CodMainPerson` values in select and check – Pரதீப் Dec 12 '14 at 13:18
  • No idea about SQL Server, but isn't "@_Value = SUM(Value)" a comparison returning TRUE/FALSE? Other dbms do "SELECT SUM(Value) INTO @_Value". – jarlh Dec 12 '14 at 13:19
  • @jarlh: the assignment is fine, that's valid T-SQL. – Mithrandir Dec 12 '14 at 13:21
  • @Mithrandir: Not correct: in this case there is no GROUP BY http://stackoverflow.com/questions/2552086/does-count-always-return-a-result/2552102#2552102 – gbn Dec 12 '14 at 13:25

1 Answers1

0
  • You assign @_Value
  • You check @_Value
  • You print @_ValorImposto

So if @_Value is not null, you don't print it...

Edit, it is not this
Therefore, the SELECT has no rows, which when SUMMed gives one row, making @_Value NULL

So test and fix the SELECT in the stored procedue: print @_CodP and @_CodMainPerson. SELECT without the SUM. Not in another query with hardcoded values: this proves nothing

Note that SUM without GROUP BY always returns exactly one row.
So @_Value will always either either a non-null value or NULL
See Does COUNT(*) always return a result? for more

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Sorry, it was worng. I edited the code, now its correct, but the @_value still getting null, that is the problem – Mucida Dec 12 '14 at 13:24
  • @Mucida: PRINT the actual filter variables, and do a SELECT without the SUM too to see what is really going on. – gbn Dec 12 '14 at 13:26