SQL Server 2008 Stored Proc not returning output from dynamic query

Question

The following stored procedure dynamically determines the columns in the specified table and returns all columns that are not null, however the stored proc is returning null despite results coming back when testing the dynamic query on its own, am I handling the dynamic query return results correctly?

CREATE PROCEDURE [dbo].[spGetTables] 
(
    @TableName    varchar(255)
,@ParamOut    varchar(2000) OUTPUT
)
AS

declare @CommaString varchar(max)
set @CommaString = ''

Declare @col varchar(255), @cmd nvarchar(max)
declare @colName varchar(2000)
SET @colName = ''

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName 

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM [' + @TableName + '] WHERE [' + @col + '] IS NULL or len([' + @col + ']) < 1) BEGIN select ''' + @col + ',''  END'

    exec sp_executesql @cmd, N'@Result varchar(max) out', @ParamOut out

    set @ParamOut = @ParamOut + @CommaString

    FETCH NEXT FROM getinfo into @col
END

PRINT 'Result: '+@ParamOut

CLOSE getinfo
DEALLOCATE getinfo
RETURN 0

gotqn · Accepted Answer · 2016-06-17 07:14:19Z

3

Change the procedure to this one and try again:

CREATE PROCEDURE [dbo].[spGetTables] 
(
    @TableName    varchar(255)
,@ParamOut    varchar(2000) OUTPUT
)
AS

declare @CommaString varchar(max)
set @CommaString = ''

Declare @col varchar(255), @cmd nvarchar(max)
declare @colName varchar(2000)
SET @colName = ''

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName 

OPEN getinfo

FETCH NEXT FROM getinfo into @col

DECLARE @TempParamOut varchar(2000) = '';

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @cmd = ' IF NOT EXISTS (SELECT top 1 * FROM [' + @TableName + '] WHERE [' + @col + '] IS NULL or len([' + @col + ']) < 1) BEGIN select @Result= ''' + @col + ',''  END'

    exec sp_executesql @cmd, N'@Result varchar(max) out', @TempParamOut out

    set @ParamOut = ISNULL(@ParamOut,'') + ISNULL(@TempParamOut,'') + @CommaString;
    set @TempParamOut = '';
    FETCH NEXT FROM getinfo into @col
END


CLOSE getinfo
DEALLOCATE getinfo
RETURN 0

In the dynamic T-SQL statement you need to assigned the value of the SELECT to the variable. Also, sometimes this output value can be NULL but STRING + NULL equals to NULL. I added a buffer variable @TempParamOut for storing the intermediate results. You are using the @ParamOut variable and overwriting its value each time the dynamic T-SQL statement is executed. That's why no concatenation is performed at all.

edited Jun 17, 2016 at 7:14

answered Jun 16, 2016 at 10:27

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joelster Over a year ago

Other than a tiny glitch where it repeats the previous non-null column for a null column, it seems to work, thanks @gotqn!

Shaun Groenewald Over a year ago

@gotqn, If it finds NULLS it retains the value in @TempParamOut , so you just need to clear it after you set @ParamOut.. set @ParamOut = ISNULL(@ParamOut,'') + ISNULL(@TempParamOut,'') + @CommaString; set @TempParamOut = '';

gotqn Over a year ago

@ShaunGroenewald Thanks. I have changed it.

SharK · Accepted Answer · 2016-06-17 08:09:30Z

2

We usually get NULL as an output when there are NULL based appends (using plus(+) sign). In order to overcome, follow below steps,

Print the dynamically created SQL statement and see what field is causing the issue
Try replacing the NULL based values with empty string ('')

edited Jun 17, 2016 at 8:09

answered Jun 16, 2016 at 10:13

SharK

2,2231 gold badge23 silver badges28 bronze badges

Comments

IVNSTN · Accepted Answer · 2016-06-16 12:47:52Z

@gotqn already found your mistake - you're not assigning anything to your output variable.

But here is another approach without cursor and with single source table scan:

DECLARE
    @sql VARCHAR(MAX),
    @TableName VARCHAR(100) = 'dbo.MyTable'


SELECT @sql = 'SELECT STUFF(''''' +
    (
        SELECT 
            '
            +ISNULL(
                    MIN(CASE WHEN t.' + QUOTENAME (c.name) + ' IS NOT NULL AND LEN(t.' + QUOTENAME (c.name) + ') >= 1 THEN '', ' + QUOTENAME (c.name) + ''' ELSE '''' END)
                    , '''')'
        FROM sys.columns c
        WHERE c.[object_id] = t.[object_id]
        ORDER BY c.column_id
        FOR XML PATH(''), TYPE 
     ).value('.', 'VARCHAR(MAX)') + ', 1, 1, '''') FROM ' 
     + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(t.name) + ' as t'
FROM sys.tables t
WHERE t.object_id = object_id(@TableName, 'U')

print @sql

EXEC (@sql)

EXEC ('select top 10 * from ' + @TableName)

Note QUOTENAME function here - better use this in place of '[' + name + ']'. And of course objects may have different schema so filtering by name may not be a secure solution.

Shaun Groenewald · Accepted Answer · 2016-06-17 08:11:42Z

Going off @gotqn 's answer.

When I ran it, it didn't order the results properly and returned with too few row. Some of this was because of garbage values in the fields eg. " " and the checking was a bit off.

Changed it to IF EXISTS instead and where NOT NULL and Len > 0

ALTER PROCEDURE [dbo].[spGetTables] 
(
    @TableName    varchar(255)
,@ParamOut    varchar(2000) OUTPUT
)
AS

declare @CommaString varchar(max)
set @CommaString = ''

Declare @col varchar(255), @cmd nvarchar(max)
declare @colName varchar(2000)
SET @colName = ''

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName 

OPEN getinfo

FETCH NEXT FROM getinfo into @col

DECLARE @TempParamOut varchar(2000) = '';

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @cmd = 'IF EXISTS (SELECT top 1 * FROM [' + @TableName + '] WHERE [' + @col + '] IS NOT NULL and len(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([' + @col + '], CHAR(10), ''''), CHAR(13), ''''), CHAR(9), '''')))) > 0) BEGIN select  @Result=''' + @col + ','' END'

    exec sp_executesql @cmd, N'@Result varchar(max) out', @TempParamOut out

    set @ParamOut = ISNULL(@ParamOut,'') + ISNULL(@TempParamOut,'') + @CommaString;
    set @TempParamOut = '';

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo
RETURN 0

Collectives™ on Stack Overflow

SQL Server 2008 Stored Proc not returning output from dynamic query

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related