列名和/或表名作为参数 [英] Column-name and/or table-name as parameters

问题描述

给定以下代码段(MS SQL):

Given following snippet (MS SQL):

DECLARE UpdateList CURSOR FOR
SELECT MyColumn FROM MyTable
OPEN UpdateList

到目前为止没什么特别的.现在我想声明两个变量，我可以在其中写入列和表的名称.当然，跟随是行不通的.我怎样才能做到这一点?

Nothing fancy so far. Now I would like to declare two variables where I can write the column's and table's name into. Following, of course, wouldn't work. How can I achieve this?

DECLARE @TableName nchar(20) = 'MyTable'
DECLARE @ColumnName nchar(20) = 'MyColumn'
DECLARE UpdateList CURSOR FOR
SELECT @ColumnName FROM @TableName
OPEN UpdateList

感谢任何提示sl3dg3

Thx for any tipps sl3dg3

推荐答案

您必须使用动态 SQL - 不能将参数用作表名或列名.所以像:

You'll have to use Dynamic SQL - you can't use parameters as table or column names. So something like:

CREATE TABLE #temp (newcol nvarchar(500)) -- Use the type you're getting out of @TableName
DECLARE @TableName nchar(20) = 'MyTable'
DECLARE @ColumnName nchar(20) = 'MyColumn'

EXEC('INSERT INTO #temp SELECT [' + @ColumnName + '] FROM [' + @TableName + ']')

DECLARE UpdateList CURSOR FOR
SELECT newcol FROM #temp
OPEN UpdateList

请记住与动态 SQL 相关的安全和性能问题 - 我不知道您将如何在此处填充变量，这样做可能存在一定的危险.

Please keep in mind the security and performance issues associated with dynamic SQL - I don't know how you'll be populating the variables, here, and there can be some definite danger in doing this.

添加完整代码.

这篇关于列名和/或表名作为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！