CODEWITHSUNDEEP
sqlsql-servert-sql
Sebastian Jung
Sebastian Jung

Reputation: 5

SELECT All Rows that are not Empty

I want a function that selects only rows from a table, that ist not empty. My first attempt:

declare @col varchar(255), @cmd varchar(max);
DECLARE @Select VARCHAR(3000)
DECLARE @Werteneu VARCHAR(3000)
DECLARE @Tablename  VARCHAR(3000)

SELECT @Tablename = 'AD'
DECLARE getinfo cursor for

SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @Tablename
SELECT @Werteneu = @Tablename
OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (SELECT TOP 1 * FROM AD WHERE ' + @col + ' IS NOT NULL) BEGIN
    END
    FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo
exec ('select ' + @Werteneu + ' from AD')

Please replace 'AD' with your Testtable. My problem is that IF EXISTS (SELECT TOP 1 * FROM AD WHERE ' + @col + ' IS NOT NULL) BEGIN begin although the row is empty.

Upvotes: 0

Views: 702

Answers (1)

pacreely
pacreely

Reputation: 1931

The EXCEPT command can be used to remove any rows that meet a specific criteria. In some environments the SORT operation can be expensive (this can be corrected with good indexing)

SELECT
    ColA
    ,ColB
    ,ColC
    ,ColD
    ,ColE
    ,ColF
    ,ColG
FROM
    mytable
EXCEPT
SELECT 
    NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL

Here's the code that would dynamically build the query.

DECLARE @tablename VARCHAR(255) = 'Employee'
DECLARE @schemaname  VARCHAR(255) = 'HumanResources'
DECLARE @qrystr NVARCHAR(2000) = ''

;with cte
    AS
    (
    SELECT
        c.column_id
        ,c.name
    FROM
        sys.objects o 
            INNER JOIN sys.columns c 
                ON
                o.object_id = c.object_id
    WHERE
        o.name = @tablename
        AND
        SCHEMA_NAME(o.schema_id) = @schemaname
    )
SELECT
    @qrystr = 
    'SELECT ' 
    + STUFF((SELECT ',' + name FROM cte ORDER BY column_id FOR XML PATH ('')), 1, 1, '') 
    + ' FROM ' 
    + @schemaname + '.' + @tablename
    + ' EXCEPT '
    + ' SELECT '
    + STUFF((SELECT ',' + 'NULL' FROM cte FOR XML PATH ('')), 1, 1, '')

Upvotes: 2

Related Questions