SQLTeam.com | Weblogs | Forums

Column is null from SP


#1

Hi,

I have the query and only do something when one of the column is not null, see the syntax below. Can anyone help please ?
Thanks

BEGIN

  IF Exists (select top 1 corp,price,custname  from mytbl where newPrice <> 0) --CONDITION

        BEGIN

              PRINT 'message1'

        END

		IF custname from the above query is not null --I don't know the sysntax for this condidion, but basiclly, the message 3 is displayed when custname is null
		BEGIN
			PRINT 'message3'
		END

  ELSE

        BEGIN

              PRINT 'message2'

        END

END


#2

Here's one way:

DECLARE @message VARCHAR(20)

CREATE TABLE mytbl
    (
        corp INT ,
        price MONEY ,
        custname VARCHAR(200) ,
        newprice MONEY
    );
INSERT INTO mytbl
VALUES
    --( 1, 42.0, 'ArthurDent', 24.0 )
    ( 2, 43.0, NULL, 0)
   ;

DECLARE @Rows INT;
WITH q
AS ( SELECT TOP 1 corp ,
            price ,
            custname
     FROM   mytbl
     WHERE  newprice <> 0 )
SELECT @message= 'Message1' + IIF(custname IS NOT NULL, CHAR(10) + CHAR(13) + 'Message3', '')
FROM q

   SET @Rows = @@Rowcount;
   IF @Rows > 0 
   PRINT @message
   ELSE 
  PRINT 'Message2'

Store procedure
#3

thank you


#4

hi

i have found another way of doing this
please correct me if i am wrong
thanks

SELECT * 
FROM   mytbl; 

    SELECT abc= CASE 
                  WHEN ( newprice <> 0 
                         AND custname IS NOT NULL ) THEN 'message3' 
                  WHEN ( newprice <> 0 
                         AND custname IS NULL ) THEN 'message1' 
                  ELSE 'message2' 
                END 
    FROM   mytbl; 


#5

It's different. With your solution you will bet Message2 for every row that does not match the criteria. If that's what you want, go for it!