Reports

Long answer (-1):
Has code block (-0.5):
User mentioned (1): @sql
User mentioned (0): @columns
Low reputation (1):

--This only shows columns that are not null, but can be amended for different criteria.

CREATE or alter PROCEDURE listcols_dynamic as --Created with help from AI bing BEGIN DECLARE @sql NVARCHAR(MAX) = 'SELECT '; DECLARE @columns NVARCHAR(MAX) = '';

--Initial Columns
SET @columns = 'static_field1,  static_field2,  '


IF EXISTS (SELECT 1 FROM mytable WHERE Col_1 IS NOT NULL)
    SET @columns = @columns + 'Col_1, ';                                
IF EXISTS (SELECT 1 FROM mytable WHERE Col_2 IS NOT NULL)
    SET @columns = @columns + 'Col_2, ';                                
IF EXISTS (SELECT 1 FROM mytable WHERE Col_3 IS NOT NULL)
    SET @columns = @columns + 'Col_3, ';                                
IF EXISTS (SELECT 1 FROM mytable WHERE Col_4 IS NOT NULL)
    SET @columns = @columns + 'Col_4, ';                                
IF EXISTS (SELECT 1 FROM mytable WHERE Col_5 IS NOT NULL)
    SET @columns = @columns + 'Col_5, ';

-- Remove the trailing comma and space if necessary
if right(@columns,2)=', '
 SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- Complete the SQL statement
SET @sql = @sql + @columns + ' FROM mytable';

--Debug
--print @sql;

-- Execute the dynamic SQL
EXEC sp_executesql @sql;

END;

79181283