SQL Server 2008 Stored Proc ne renvoie pas la sortie d'une requête dynamique

Question

La procédure stockée suivante détermine dynamiquement les colonnes de la table spécifiée et renvoie toutes les colonnes qui ne sont pas nulles, cependant la procédure stockée renvoie des résultats nuls malgré les résultats obtenus lors du test de la requête dynamique seule, est-ce que je gère correctement le retour des résultats de la requête dynamique ?

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

Answer 1

Nous obtenons généralement NULL en sortie lorsqu'il y a des ajouts basés sur NULL (en utilisant le signe plus(+)). Pour y remédier, suivez les étapes ci-dessous,

1. Imprimez l'instruction SQL créée dynamiquement et voyez quel champ est à l'origine du problème.
2. Essayez de remplacer les valeurs basées sur NULL par une chaîne vide ('').

Answer 2

Remplacez la procédure par celle-ci et réessayez :

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

Dans la dynamique T-SQL vous devez affecter la valeur de l'instruction SELECT à la variable. De plus, parfois, cette valeur de sortie peut être NULL mais STRING + NULL est égal à NULL . J'ai ajouté une variable tampon @TempParamOut pour stocker les résultats intermédiaires. Vous utilisez le @ParamOut et en écrasant sa valeur à chaque fois que la variable dynamique T-SQL est exécutée. C'est pourquoi aucune concaténation n'est effectuée.

Answer 3

@gotqn a déjà trouvé votre erreur - vous n'affectez rien à votre variable de sortie.

Mais voici une autre approche sans curseur et avec un balayage de table à source unique :

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 ici - il vaut mieux l'utiliser à la place de la fonction '[' + name + ']' . Et bien sûr, les objets peuvent avoir différents schema Le filtrage par nom n'est donc pas forcément une solution sûre.

Answer 4

A partir de la réponse de @gotqn.

Lorsque je l'ai exécuté, il n'a pas ordonné les résultats correctement et est revenu avec trop peu de lignes. Une partie de ce problème était due à des valeurs inutiles dans les champs, par exemple " ", et la vérification était un peu fausse.

Changé en IF EXISTS à la place et où NOT NULL et 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