How can i do this in T-SQL?

[aspijker]

iS THE FOLLOWING POSSIBLE IN t-sql?
basically i want to check all the field in a database if they are not null and print the message.

DECLARE @MESSAGE AS varchar (100)
SELECT
@MESSAGE
CASE FIELD1
WHEN NULL OR NOT Like '%[0-9]%' THEN @MESSAGE = 'FIELD1 is not numeric or NULL',
CASE FIELD2
WHEN NULL OR NOT Like '%[0-9]%' THEN @MESSAGE = 'FIELD2 is not numeric or NULL',
CASE FIELD3
WHEN NULL THEN @MESSAGE = FIELD3 is NULL'
FROM table

PRINT MESSAGE

Thanks for your help!

Greetz! Salsa

 

[bborissov]

DECLARE @MESSAGE AS varchar (100)
SET @MESSAGE = NULL
SELECT @MESSAGE = ISNULL(@MESSAGE+CHAR(13)+CHAR(10),'')+
    CASE WHEN FIELD1 IS NULL OR
              FIELD1 NOT Like '%[0-9]%'
              THEN 'FIELD1 is not numeric or NULL'
         ELSE '' END+
    CASE WHEN FIELD2 IS NULL OR
              FIELD2 NOT Like '%[0-9]%'
              THEN 'FIELD2 is not numeric or NULL'
         ELSE '' END+
    CASE WHEN FIELD3 IS NULL
              THEN 'FIELD3 is NULL'
         ELSE '' END 
FROM table

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[aspijker]

Really thanks!! I will test it at home, but this is exactly what I need!

Have a very good weekend

Regards, Salsa

 

[aspijker]

Hi Boris,

Hmm, I still can't get it to work: First when it's a numeric field it complains it can't be converted. if I uncomment that part, it runs fine, only my variable @MESSAGE is empty.

Regards, Salsa

DECLARE @MESSAGE AS varchar (100)
SET @MESSAGE = NULL
SELECT @MESSAGE = ISNULL(@MESSAGE+CHAR(13)+CHAR(10),'')+
-- CASE WHEN FIELD1 IS NULL OR
-- FIELD1 NOT Like '%[0-9]%'
-- THEN CONVERT(VARCHAR(50), 'FIELD1 is not numeric or NULL')
-- ELSE '' END+
-- CASE WHEN FIELD2 IS NULL OR
-- FIELD2 NOT Like '%[0-9]%'
-- THEN CONVERT(VARCHAR(50), 'FIELD2 is not numeric or NULL')
-- ELSE '' END+
CASE WHEN FIELD3 IS NULL
THEN CONVERT(VARCHAR(50), FIELD3 is NULL')
ELSE '' END
FROM ACC

PRINT @MESSAGE

 

[bborissov]

If it is a numeric field you should not use
FIELDxxx NOT Like '%[0-9]%', IS NULL is enough:
Let's say Field2 is numeric field:

DECLARE @MESSAGE AS varchar (100)
SET @MESSAGE = NULL
SELECT @MESSAGE = ISNULL(@MESSAGE+CHAR(13)+CHAR(10),'')+
    CASE WHEN FIELD1 IS NULL OR
              FIELD1 NOT Like '%[0-9]%'
              THEN 'FIELD1 is not numeric or NULL'
         ELSE '' END+
    CASE WHEN FIELD2 IS NULL
              THEN 'FIELD2 is not numeric or NULL'
         ELSE '' END+
    CASE WHEN FIELD3 IS NULL
              THEN 'FIELD3 is NULL'
         ELSE '' END
FROM table

If the field is numeric the it will be NULL or numeric it can't contain any other char than digit in it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP