问题描述
我们在数据库中有一个表,该表有一个名为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 usefieldName
by itself.
这篇关于动态选择语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!