Probably need cursor

I have one obviously simple problem, but cannot find good solution, so I would appreciate community help.

Let's say that my table has three columns: DealID, TransID and Number. And let's say that I have 4 rows, value for DealID is same in all 4 rows, TransID has different value in each row (and data is ordered ascending by this column) and Number has some value in first row and NULLs in all other rows.

My simple question is: For exact Trans_Id, how to determine in sproc for all Trans_Id values greater than current, are all values for Number NULLs? I.e. I want to know is there any Number value that is different from NULL for any other Trans_Id that is greater than current Trans_Id value.

T开发者_运维问答nX in advance!

Nemanja

How's this:

SELECT * FROM mytable
WHERE DealID = (SELECT DealID FROM mytable WHERE TransID = @transID)
AND TransID > @transID
AND Number IS NOT NULL

Try something like this (I assume dealID is one of parameters):

-- return 1 if there not null numbers for given @dealID and @tranID
CREATE PROC checkNullNumbers (
   @dealID INT,
   @transID INT
)
AS 
BEGIN
   DECLARE 
      @result INT

   SELECT TOP 1 1
   FROM 
    mytable
   WHERE
     dealID = @dealID
     AND transID > @transID
     AND number IS NOT NULL

   SET @result = @@ROWCOUNT

   SELECT @result AS result
END