时间戳

首页 > 解决方案 > 从函数或存储过程返回动态 SQL 表以在查询中使用

sql-server - 从函数或存储过程返回动态 SQL 表以在查询中使用

问题描述

我有许多表具有类型为 DATETIME 的“已删除”字段，我使用该字段将记录标记为已删除（而不是完全删除它们）。

我经常发现自己从这些表中请求数据并希望排除已删除的记录。我现在正在做的事情是这样的：

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

是否有可能有一个存储过程/函数，我可以将表名传递给它并返回过滤后的表？就像是：

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

我尝试使用表值函数来执行此操作，但据我所知，我需要定义结果表的列。我不能这样做，因为它并不总是我使用该函数的同一张表。

标签： sql-serverfunctionstored-proceduresdynamic

解决方案

在我看来，你不能用表值函数来做到这一点，因为它们需要静态表声明。不幸的是，您的表声明不一样。因此，您无法借助表值函数来实现这一点。在以下存储过程中，可能有助于解决您的问题。

这个存储过程基于以下步骤；

  1. 生成声明的表脚本
  2. 创建一个全局临时文件，它与声明的表具有相同的结构
  3. 将原始表数据填充到临时表中
  4. 返回临时表的结果集

注意：此查询使用GetTableCreateScript存储过程来获取声明的表创建脚本。

已编辑：为 SQL Server 2017STRING_AGG的较低版本删除并添加了以组合行。XML PATH

  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    |
    +------+---------+

推荐阅读