Solved

Stored procedure nested output

Posted on 2009-05-05
3
535 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:BrightRaven
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 24303605
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
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 24303617
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
 

Author Closing Comment

by:BrightRaven
ID: 31577965
Many thanks. Works a treat.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Sum Over Multiple Tables 20 33
Need help with convert Informix SQL SELECT statement to Microsoft SQL 1 26
Need SSIS project 2 29
SQL Syntax 6 32
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question