如何在select语句中使用声明游标的变量 [英] How to use a variable in a select statement for a declare cursor

问题描述

我有这个变量@cols（varchar），它包含我需要查询的表的列名。如何在函数中使用此变量以正确的方式声明游标？

I have this variable @cols (varchar) which contains the column names of a table I need to query. How can I use this variable in a function to declare a cursor the right way?

-- code where I fill up @cols
if len(@cols) = 0
    set @ret = 0
else
    begin
        set @cols = substring(@cols, 2, 1000)
        DECLARE Data CURSOR FOR 
	SELECT @cols from OTCriterium WHERE ParentPK = @PKParentCriterium
	OPEN Data
        -- other stuff
	set @ret = 1
    end
-- other code

提前致谢！

Thanks in advance!

推荐答案

虽然你可以使用 EXECUTE [ ^ ]运行动态SQL语句，我是如果这是一个好方法真的很可疑。



如果你是动态的话定义结果集列不会因为你不知道列而难以从光标中选择值吗？



当你有条件变化时，动态sql是可行的，但我会考虑其他方法。



- 编辑

添加了一个小例子：使用UNPIVOT计算动态列的聚合 [ ^ ]

While you can use EXECUTE[^] to run a dynamic SQL statement, I''m really suspicious if this is a good approach.

If you dynamically define the result set columns wouldn''t this result into a situation where you have difficulties in selecting the values from the cursor since you don''t know the columns?

Dynamic sql could be feasible when you have changing conditions, but I would consider other kind of approaches for this.

-- EDIT
Added a small example: Calculate aggregates for dynamic columns using UNPIVOT[^]

将查询构建为字符串并将其作为动态sql语句执行。请参阅此文章作为一系列可能性：在存储中构建动态SQL程序 [ ^ ]

Build the query as string and execute it as dynamic sql statement. See this article as a good collection of possibilities: Building Dynamic SQL In a Stored Procedure[^]

这里 [ ^ ]您有一个解决方案，可以使用xml支持将表行转换为名称 - 值对。您可以使用相同的方法仅过滤掉列表中的字段，并按照您的提及汇总其值。

Here[^] you have a solution to transform a table row to name-value pairs using the xml support. You can use the same approach to filter out only the fields that are in the list, and summarize their values as you mentioned.

这篇关于如何在select语句中使用声明游标的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！