BrightRaven
asked on
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.
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks. Works a treat.
You can fill a temptable and return it at the end of the sp.
/peter
Open in new window