如何在SQL Server中动态创建查询 [英] How to LOOP a query that is created dynamically in SQL Server
问题描述
我有一个存储过程,在该过程中,我从表中获取数据库名称,然后尝试从该数据库名称创建动态查询并获取结果.获取结果后,我需要循环这些结果,以便进一步执行查询以获得所需结果
I have a stored procedure where I am getting the database name from a table and then trying to create a dynamic query from this database name and fetching the results. Once the results are fetched I need to loop these results for further queries to be executed to get the desired result
USE DATABASE1
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [User].[update_client_details]
AS
DECLARE @clientdata CURSOR,
@clientid INT,
@SQL NVARCHAR(2000),
@uid INT,
@isFirst INT,
@isTemp INT,
@inactive INT,
@createdDate Date
BEGIN
DECLARE C CURSOR LOCAL FOR
SELECT clientuserid FROM USER.queen_client
OPEN C
FETCH NEXT FROM C INTO @clientid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'SELECT userid, @isFirst=isfirst, @isTemp=istemp, @inactive=inactive, @createdDate=createddate FROM ' +QUOTENAME(@clientid)+'.USER.queen_user;';
EXEC sys.sp_executesql @SQL, N'@inactive int OUTPUT, @uid int OUTPUT, @isFirst int OUTPUT, @isTemp int OUTPUT, @createdDate date OUTPUT', @inactive OUTPUT, @uid OUTPUT, @isFirst OUTPUT, @isTemp OUTPUT, @createdDate OUTPUT;
// @SQL returns multiple rows - I need to loop the output of @SQL
// UPDATE QUERY BASED ON IF CONDITION COMES HERE
FETCH NEXT FROM C INTO @clientid
END
CLOSE C
DEALLOCATE C
END
由于SQL查询是动态的-我如何循环此动态查询的输出.
As the SQL query is dynamic - how do I loop the output of this dynamic query.
推荐答案
由于SQL查询是动态查询,因此如何循环此动态查询的输出.
As the SQL query is dynamic How do I loop the output of this dynamic query.
在动态查询之外创建一个临时表,并将其插入到动态查询中.然后您可以从临时表中读取.
Create a temp table outside of the dynamic query, and insert into it in the dynamic query. Then you can read from the temp table.
SET @SQL = N'
INSERT INTO #tempUser(userId,IsFirst,IsTemp,inactive,createddate)
SELECT userid, isfirst, istemp, inactive, createddate
FROM ' +QUOTENAME(@clientid)+'.USER.queen_user;';
但是更好的整体方法可能是在所有表的单独数据库中创建分区视图. EG
But a better overall approach might be to create a partitioned view in a seperate database over all the tables. EG
create view queen_user
as
select 123 clientId, userid, isfirst, istemp, inactive
from Client123.USER.queen_user
union all
select 124 clientId, userid, isfirst, istemp, inactive
from Client124.USER.queen_user
union all
. . .
union all
select 999 clientId, userid, isfirst, istemp, inactive
from Client999.USER.queen_user
并且有一个可以在每次添加新客户端数据库时对其进行更改的过程.
And have a procedure that alters it any time a new client db is added.
这篇关于如何在SQL Server中动态创建查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!