本文介绍了动态选择语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

08-22 20:44