Stored procedure nested output

I have a stored procedure that I want to return all those fields in a table that are null throughout the records. Basically will show unused fields in a table. I am passing @TableName as an input parameter and intend to use the output as a simple column on a report in SSRS.

PROBLEM: I can't get the output of a stored procedure. It has a nested execute statement and is only returning one field.
SProc is below.

Grateful thanks in advanced.
USE [Metastorm]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReportPackNullFields]    Script Date: 05/05/2009 11:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ReportPackNullFields]
	@TableName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @query nvarchar(max);
	DECLARE @Colname nvarchar(100);
    DECLARE @column sysname;
    DECLARE columns_cursor CURSOR FOR
    	SELECT c.name
    	FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
    	WHERE t.name = @tablename AND c.is_nullable = 1;
    OPEN columns_cursor;
    FETCH NEXT FROM columns_cursor INTO @column;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    	SET @query = N'
    	DECLARE @c int;
		DECLARE @column nvarchar(100);
		SELECT @c = COUNT(*) FROM ' + @tablename + ' WHERE ' + @column + N' IS NOT NULL
    	IF (@c = 0)
			SELECT (''' + @column + N''') AS NullColumn;'
		EXECUTE (@query);
		FETCH NEXT FROM columns_cursor INTO @column;
    END
    CLOSE columns_cursor;
    DEALLOCATE columns_cursor;
    SET NOCOUNT OFF;
END;

Open in new window

BrightRavenBusiness AnalystAsked:
Who is Participating?
 
pivarCommented:
Sorry, a typo. Remove END after EXECUTE(@query), like this
USE [Metastorm]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReportPackNullFields]    Script Date: 05/05/2009 11:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ReportPackNullFields]
        @TableName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
 
CREATE table #result (Nullcolumn varchar(100))
 
        DECLARE @query nvarchar(max);
        DECLARE @Colname nvarchar(100);
    DECLARE @column sysname;
    DECLARE columns_cursor CURSOR FOR
        SELECT c.name
        FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
        WHERE t.name = @tablename AND c.is_nullable = 1;
    OPEN columns_cursor;
    FETCH NEXT FROM columns_cursor INTO @column;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @query = N'
        DECLARE @c int;
                DECLARE @column nvarchar(100);
                SELECT @c = COUNT(*) FROM ' + @tablename + ' WHERE ' + @column + N' IS NOT NULL
        IF (@c = 0)
                SELECT (''' + @column + N''') AS NullColumn;'
        INSERT INTO #result
                EXECUTE (@query);
                FETCH NEXT FROM columns_cursor INTO @column;
    END
    CLOSE columns_cursor;
    DEALLOCATE columns_cursor;
 
    SELECT * FROM #result
 
    SET NOCOUNT OFF;
END;

Open in new window

0
 
pivarCommented:
Hi,

You can fill a temptable and return it at the end of the sp.

/peter


USE [Metastorm]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReportPackNullFields]    Script Date: 05/05/2009 11:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ReportPackNullFields]
        @TableName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
 
CREATE table #result (Nullcolumn varchar(100))
 
        DECLARE @query nvarchar(max);
        DECLARE @Colname nvarchar(100);
    DECLARE @column sysname;
    DECLARE columns_cursor CURSOR FOR
        SELECT c.name
        FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
        WHERE t.name = @tablename AND c.is_nullable = 1;
    OPEN columns_cursor;
    FETCH NEXT FROM columns_cursor INTO @column;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @query = N'
        DECLARE @c int;
                DECLARE @column nvarchar(100);
                SELECT @c = COUNT(*) FROM ' + @tablename + ' WHERE ' + @column + N' IS NOT NULL
        IF (@c = 0)
                SELECT (''' + @column + N''') AS NullColumn;'
        INSERT INTO #result
                EXECUTE (@query);
        END
                FETCH NEXT FROM columns_cursor INTO @column;
    END
    CLOSE columns_cursor;
    DEALLOCATE columns_cursor;
 
    SELECT * FROM #result
 
    SET NOCOUNT OFF;
END;

Open in new window

0
 
BrightRavenBusiness AnalystAuthor Commented:
Many thanks. Works a treat.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.