Simplify Query help pls...

[peac3]

Hi guys,

I have a task to check the mandatory columns whether it is NULL or blank and stored them into exception table.
Here is the query statement for one particular column and it's working.

	UPDATE LOAD_TABLE
	SET ERR_F = 'W'
	OUTPUT INSERTED.*,
		'B2',
		GETDATE(),
		'Warning :	EC1 Null or blank non-nullable field : [SECC]',
		1
	INTO LOAD_EXCEPTION
	WHERE
		SECC = '' OR SECC IS NULL

I have to check NULL or blank for like 20-30 other columns in LOAD_TABLE and If I repeat those query 20-30 times it wouldn't look very nice in the stored proc.
Hence I was wondering whether I could make the column name dynamic.

something like:

	UPDATE LOAD_TABLE
	SET ERR_F = 'W'
	OUTPUT INSERTED.*,
		'B2',
		GETDATE(),
		'Warning :	EC1 Null or blank non-nullable field :' + Dynamic_column_name,
		1
	INTO LOAD_EXCEPTION
	WHERE
		(SECC = '' OR SECC IS NULL) OR (COLUMN2 = '' OR COLUMN2 IS NULL) OR ...

Thanks in advance!

 

[bborissov]

ISNULL((SECC, '') = ''

Borislav Borissov
VFP9 SP2, SQL Server

 

[peac3]

Hi bborissov,

Thanks for your reply,

But the question would be simplify repetitive query to check some(20-30) columns NULL or blank for number of columns and store into exception table

If I wrote the query as below it wouldn't look efficient...

UPDATE LOAD_TABLE
	SET ERR_F = 'W'
	OUTPUT INSERTED.*,
		'B2',
		GETDATE(),
		'Warning :	EC1 Null or blank non-nullable field : COLUMN1',
		1
	INTO LOAD_EXCEPTION
	WHERE
		COLUMN1 = '' OR COLUMN1 IS NULL 
		

UPDATE LOAD_TABLE
	SET ERR_F = 'W'
	OUTPUT INSERTED.*,
		'B2',
		GETDATE(),
		'Warning :	EC1 Null or blank non-nullable field : COLUMN2',
		1
	INTO LOAD_EXCEPTION
	WHERE
		COLUMN2 = '' OR COLUMN2 IS NULL 
		
		
UPDATE LOAD_TABLE
	SET ERR_F = 'W'
	OUTPUT INSERTED.*,
		'B2',
		GETDATE(),
		'Warning :	EC1 Null or blank non-nullable field : COLUMN5',
		1
	INTO LOAD_EXCEPTION
	WHERE
		COLUMN5 = '' OR COLUMN5 IS NULL 
		
		
UPDATE LOAD_TABLE
	SET ERR_F = 'W'
	OUTPUT INSERTED.*,
		'B2',
		GETDATE(),
		'Warning :	EC1 Null or blank non-nullable field : COLUMN8',
		1
	INTO LOAD_EXCEPTION
	WHERE
		COLUMN8 = '' OR COLUMN8 IS NULL 		
		
...

Thanks

 

[bborissov]

DECLARE @i int
DECLARE @sql nvarchar(4000)
SET @i = 1
WHILE @i < 30
   BEGIN
       SET @sql = N'UPDATE LOAD_TABLE SET ERR_F = ''W''
	                OUTPUT INSERTED.*, ''B2'', GETDATE(),
		                   ''Warning :	EC1 Null or blank non-nullable field : COLUMN'+CAST(@i as nvarchar(2))+N' '', 1
	                INTO LOAD_EXCEPTION
	                WHERE ISNULL(COLUMN'+CAST(@i as nvarchar(2))+','''') = '''' '
       EXEC sp_executesql  @sql 
       SET @i = @i + 1
   END

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server