Solved

Stored procedure nested output

Posted on 2009-05-05
3
526 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
  • 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now