动态选择语句 [英] Dynamic select statement

问题描述

我们在数据库中有一个表，该表有一个名为fieldName"的列.在 fieldName 是另一个表中的列的名称.基本上，这是用户界面的一部分，用户可以在其中向表单添加问题，当发生这种情况时，我们将该信息存储在 fieldName 中，然后创建一个自定义表，其列名将匹配一个字段名.

We have a table in the database and this table has a column called 'fieldName'. In fieldName is a name of a column in another table. Basically this is part of a UI where a user can add a question to a form and when that happens we store that information in fieldName and then create a custom table with a column name that will match one field name.

我想要做的是生成一个动态 SQL 语句来获取所有 fieldNames 并将其构建到 SQL 语句中.到目前为止，我有这个:

What I want to do is generate a dynamic SQL statement to get all the fieldNames and build that into a SQL statement. So far I have this:

DECLARE @CFTable INT
DECLARE @columnNames VARCHAR(8000) 

SET @CFTable = 693


SELECT @columnNames = COALESCE(@columnNames + ', ', '') + CONVERT(VARCHAR(25),fieldName )
FROM [FSM_CustomFormColumn]
WHERE CustomFormID = @CFTable

print @columnNames

上面的结果就是列名，像这样:

The result of the above is the column names, like this:

HearAboutEvent, ParticipatedBefore, WeatherDependent, NonRefundable, TransferFee

因此，如果我将所有这些列名都存储在 @columnNames 变量中，那么从这里我想做这样的事情:

So if I have all those column names stored in the @columnNames variable, from here I want to do something like this:

select @columnNames from table

这是我不知道如何开始工作并需要帮助的地方.

This is what I don't know how to get working and need help with.

推荐答案

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT ' + @ColumnNames + N' FROM TABLE;'

EXEC(@SQL);

• 在您的原始 SQL 中，将 @ColumnNames 更改为定义为 NVARCHAR(MAX).在内部，所有对象名称等都有一个 SYSNAME 类型，它等同于 NVARCHAR(128).
• 您也不应该需要 CONVERT(VARCHAR(25), fieldName)，只需单独使用 fieldName.
• In your original SQL, change @ColumnNames to be defined as NVARCHAR(MAX). Internally all object names, etc have a type of SYSNAME which equates to NVARCHAR(128).
• You also should not need the CONVERT(VARCHAR(25), fieldName), just use fieldName by itself.

这篇关于动态选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！