T-SQL function in Select to Only Return Columns with Values

Question

I have a query that will return only columns with values. How do I add that to a function so I can use that with any query? Would it be a function in the where clause.

create table test1
(
    s_no int not null,
    name varchar(10) not null,
    address varchar(10) null,
    emailid varchar(100) null
)

insert into test1 (s_no, name)
values (1,'A'),(2,'B'),(3,'C')

declare @column_list varchar(8000),
        @counter int

set @column_list = ''
set @counter = 0

while (Select max(colid) from syscolumns where id = object_id('test1') and isnullable= 0) > @counter
begin
    select @counter = min(colid) 
    from syscolumns 
    where id = object_id('test1') 
      and isnullable = 0 
      and colid > @counter

    select @column_list = @column_list + ',' + (Select name from syscolumns where id = object_id('test1') and isnullable= 0 and colid = @counter)
end

select @column_list = SUBSTRING(@column_list, 2, len(@column_list))

declare @sql varchar(8000)
select @sql = 'select ' + @column_list + ' from test1'

print @sql

exec (@sql)

SELECT * FROM [dbo].[test1]

Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. — HABO, Sep 21 '22 at 15:32

GuidoG · Answer 1 · 2022-09-22T07:13:37.127

I guess you could make a stored procedure where you provide the table name as parameter, and then build your query like you are doing already

create procedure ShowOnlyFilledColumns (@tablename varchar(100)) as
begin
     set nocount on

     declare @column_list varchar(8000),
             @counter int
     set @column_list = ''
     set @counter = 0
     while (Select max(colid) from syscolumns where id = object_id(@tablename) and isnullable= 0) > @counter
     begin
          select @counter = min(colid) from syscolumns where id = object_id(@tablename) and isnullable= 0 and colid > @counter
          select @column_list = @column_list + ',' + (Select name from syscolumns where id = object_id(@tablename) and isnullable= 0 and colid = @counter)
     end
     select @column_list = SUBSTRING(@column_list,2,len(@column_list))
     declare @sql varchar(8000)
     select @sql = 'select ' + @column_list + ' from ' + @tablename
--print @sql
     exec (@sql)     
end

and use it like this

exec ShowOnlyFilledColumns 'test1'

See the complete example in this DBFiddle

EDIT: The OP asked how he can add joins on this

There are a few tricks to join with a stored procedure, for example in these answers

However, this won't work on this solution, because it requires to create a temp table to store the result of the procedure.
The trick looks like this

-- create a temporary table to store the results of the procedure
CREATE TABLE #Temp (
  s_no int not null,
  name varchar(10) not null,
  address varchar(10) null,
  emailid varchar(100) null
)

-- call the procedure and store the result in the temporary table
INSERT INTO #Temp
exec ShowOnlyFilledColumns 'test1'

-- now I can query the temp table, and join on it and write a where clause, and I can do whatever I want
select * from #Temp

Now, this won't work in this case, because the stored procedure can return different columns every time you run it, and to make the insert into #Temp exec ShowOnlyFilledColumns 'test1' work, the table #Temp must have the same number and type of columns as the procedure returns. And you just don't know that.

This will not be easy, as I have tried to explain in the EDIT in my answer — GuidoG, Sep 22 '22 at 07:14

T-SQL function in Select to Only Return Columns with Values

1 Answers1