在我的数据库中,我有一个表(SqlQueries),其中列(ID,SQLText)是存储的值
ID SQLText
1 SELECT FIELD1 as FD1, FiELD2 as FD2 FROM TABLE1;
2 SELECT FIELD3 as FD3, FiELD4 as FD4 FROM TABLE2;
出于某些原因,我在存储过程中使用了一个游标,它循环这个表,并使用以下代码执行我的SQL:
VAR @SQLText NVARCHAR(MAX)
SET @SQLTEXT = (SELECT SQLText FROM SqlQueries where ID = 1);
EXEC (@SQLTEXT);
现在,我想以下方式从这个执行查询中选择数据:
FIELDNAME FIELDVALUE
FD1 VALUE1
FD1 VALUE2
FD2 VALUE1
使用T-SQL中的存储过程或函数是否可以做到这一点?
谢谢
发布于 2018-06-13 15:07:30
我不认为如果查询中查询的字段的数量可以改变(或者至少不是很容易),这是不可能的。
我可以想到的一种方法是,每次循环时解析查询,以获取字段的名称。不太整洁,但我想不出更好的办法
下面我已经做了一个示例-希望它对你有用(如果您愿意,可以用游标替换while循环),如果你愿意,可以将它放到存储过程中。
-- set up a temporary table for test purpose
CREATE TABLE #table(ID INT, Field1 NVARCHAR(255), Field2 NVARCHAR(255), Field3 NVARCHAR(255), Field4 NVARCHAR(255))
-- set up some test data
INSERT #table
( ID, Field1, Field2, Field3, Field4 )
VALUES ( 1, 'result 1.1', 'result 1.2', 'result 1.3', 'result 1.4')
DECLARE @sqlQueries TABLE (ID INT, SqlText NVARCHAR(255))
INSERT @sqlQueries
( ID, SqlText )
VALUES ( 1, 'SELECT Field1 as FD1, Field2 as FD2 FROM #table' )
,( 2, 'SELECT Field3 as FD3, Field4 as FD4 FROM #table' )
DECLARE @sqlText NVARCHAR(MAX)
DECLARE @values TABLE(v1 NVARCHAR(255), v2 NVARCHAR(255))
DECLARE @results TABLE(field NVARCHAR(255), value NVARCHAR(255))
DECLARE @i INT = 1
DECLARE @field1 NVARCHAR(255), @field2 NVARCHAR(255), @startIndex INT, @charsToTake INT
-- begin loop
WHILE @i <= (SELECT MAX(ID) FROM @sqlQueries)
BEGIN
SELECT @sqlText = SqlText FROM @sqlQueries AS sq WHERE ID = @i
INSERT @values
EXEC( @sqlText)
-- work out the field aliases used in the query. Assume exactly 2 field names,
-- and that the queries all follow the exact pattern as in the examples
SELECT @startIndex = CHARINDEX('as ', @sqlText) + 3
SELECT @charsToTake = CHARINDEX(',', @sqlText) - @startIndex
SELECT @field1 = SUBSTRING(@sqlText, @startIndex, @charsToTake)
SELECT @startIndex = CHARINDEX('as ', @sqlText, @startIndex) + 3
SELECT @charsToTake = CHARINDEX(' FROM', @sqlText) - @startIndex
SELECT @field2 = SUBSTRING(@sqlText, @startIndex, @charsToTake)
-- get the results of the query, and then for each of the 2 values insert into a result field
INSERT @results
( field, value )
SELECT @field1, v.v1
FROM @values AS v
UNION SELECT @field2, v.v2
FROM @values AS v
DELETE @values
SET @i = @i + 1
END
-- end loop
-- finally select from the results
SELECT * FROM @results AS r
DROP TABLE #table
你可以确认这将返回预期的结果:
field value
FD1 result 1.1
FD2 result 1.2
FD3 result 1.3
FD4 result 1.4
发布于 2018-06-13 15:32:07
是的,你可以将代码放入存储过程中。我以为你希望将ID作为参数传递,但你可以修改它。
CREATE PROCEDURE YourProcedureName
(
@ID int
)
AS
BEGIN
DECLARE @SQLText NVARCHAR(MAX)
SELECT @SQLText = SQLText FROM SqlQueries WHERE ID = @ID
IF @SQLText IS NOT NULL
EXEC sp_executesql @SQLText
END
https://stackoverflow.com/questions/-100004887
复制相似问题