Renvoie une table SQL dynamique à partir d'une fonction ou d'une procédure stockée pour l'utiliser dans une requête.

Question

J'ai un certain nombre de tables qui ont un champ "Deleted" de type DATETIME que j'utilise pour marquer les enregistrements comme supprimés (au lieu de les supprimer totalement).

Je me retrouve souvent à demander des données à partir de ces tables et à vouloir exclure les enregistrements supprimés. Ce que je fais en ce moment est quelque chose comme :

SELECT A.* 
FROM (SELECT * FROM [TargetTable] WHERE (Deleted IS NULL)) AS A
WHERE (.........)

Est-il possible d'avoir une procédure/fonction stockée à laquelle je peux passer le nom de la table et obtenir la table filtrée ? Quelque chose comme :

SELECT A.*
FROM fnExcludeDeleted('MyTable') AS A
WHERE (.........)

J'ai essayé de le faire à l'aide d'une fonction de valeur de tableau, mais d'après ce que j'ai pu comprendre, il me faudrait définir les colonnes du tableau résultant. Je ne peux pas le faire parce que ce n'est pas toujours la même table sur laquelle j'utilise la fonction.

Answer 1

À mon avis, vous ne pouvez pas le faire avec les fonctions à valeur de tableau, car elles nécessitent des déclarations de tableau statiques. Malheureusement, vos déclarations de tables ne sont pas les mêmes. Par conséquent, vous ne pouvez pas réaliser ceci avec l'aide des fonctions de valeur de table. La procédure stockée suivante peut vous aider à résoudre votre problème.

Cette procédure stockée est basée sur les étapes suivantes ;

1. Générer le tableau déclaré script.
2. Créer un temporaire global, qui est formé avec la même structure que le temporaire déclaré. table
3. Remplir les données de la table originale dans une table temporaire
4. Retourner le jeu de résultats de la table temporaire

Nota: Cette requête utilise GetTableCreateScript procédure stockée afin d'obtenir la création de la table déclarée script.

ÉDITION : STRING_AGG supprimé pour la version inférieure de l SQL Server 2017 et a ajouté le XML PATH afin de combiner les rangs.

  DROP TABLE IF EXISTS TargetTable1
GO
CREATE TABLE TargetTable1 (Col1 INT , Deleted DATE)
GO
---Populating test data---
INSERT INTO TargetTable1 VALUES (1,GETDATE())
INSERT INTO TargetTable1 VALUES (2,NULL)
INSERT INTO TargetTable1 VALUES (3,GETDATE())
INSERT INTO TargetTable1 VALUES (4,NULL)
GO
CREATE OR ALTER PROC DynamicSQL 
@TableName AS VARCHAR(200)
AS
DROP TABLE IF EXISTS  ##TempDynamic
DECLARE @SQLDynamicTable AS VARCHAR(MAX)
DECLARE @SQLDynamicRows AS VARCHAR(MAX)
CREATE TABLE #DynamicSQL (S VARCHAR(MAX))

INSERT INTO #DynamicSQL
EXEC    [dbo].GetTableCreateScript @TableName --->Generate Table Definition
---You can find GetTableCreateScript  create script https://www.c-sharpcorner.com/blogs/generate-table-defination-in-sql-server-without-gui

--SELECT @SQLDynamicTable=REPLACE(STRING_AGG(S,' '),@TableName, '##TempDynamic') FROM #DynamicSQL ----->Create TempTable for SQL Server 2017 and upper version

SELECT  @SQLDynamicTable=STUFF((
         SELECT ' ' + s
            FROM #DynamicSQL 
            FOR XML PATH('')
         ), 1, 1, '')

SET @SQLDynamicTable = REPLACE(@SQLDynamicTable,@TableName, '##TempDynamic')
PRINT @SQLDynamicTable
EXEC (@SQLDynamicTable)
SET @SQLDynamicRows = 'INSERT INTO ##TempDynamic SELECT * FROM ' + @TableName
EXEC(@SQLDynamicRows)
SELECT * FROM ##TempDynamic
WHERE Deleted IS NULL

GO 
EXEC DynamicSQL 'TargetTable1'

    +------+---------+
    | Col1 | Deleted |
    +------+---------+
    |    2 | NULL    |
    |    4 | NULL    |
    +------+---------+