在sql的where子句中使用变量作为列名

I want to use a variable as column name in my SQL query.

我想在我的SQL查询中使用变量作为列名。

Problem:

I can not use dynamic SQL as the SQL query that I have is inside of a user defined inline table function.

我不能使用动态SQL作为我在用户定义内联表函数内的SQL查询。

So, 1. Can not use dynamic SQL 2. Can not replace this function with a stored procedure as this is to be called from user defined functions only

因此,1。不能使用动态SQL 2.不能用存储过程替换此函数,因为这只是从用户定义的函数调用

What are other possible alternatives?

还有什么其他可能的选择?

My code is as follows:

我的代码如下:

create function MatchStringPercent(@parameterFromUser nvarchar(20), @parameterInTable nvarchar(20))
RETURNS table As Return 
SELECT Top(1)
cast(LEN(@parameterInTable) as float)/(Abs(LEN(@parameterFromUser) - LEN(@parameterInTable)) + LEN(@parameterInTable))* 100 As Match
FROM Demo
WHERE @parameterInTable = LEFT( @parameterFromUser , LEN(@parameterInTable) ) 
ORDER BY LEN(@parameterInTable) DESC 
Go

Note: This is a repost. I did ask this question here and was told this is not possible. May be. but just wanted to know if there is any damn possible alternative.

注意:这是转贴。我确实在这里问过这个问题并被告知这是不可能的。也许。但只是想知道是否有任何该死的替代品。

1 个解决方案

#1

Your only real option is a giant case statement:

你唯一真正的选择是一个巨大的案例陈述:

where (case when @parameterInTable = 'col1' then col1
            when @parameterInTable = 'col2' then col2
            . . .
       end) = . . .

Unfortunately, the cleaner solution is to use dynamic SQL and you can't do this from a function.

不幸的是,更干净的解决方案是使用动态SQL,而不能通过函数执行此操作。

I did once see some strange mechanism where a function can call xp_cmdshell, then using a cmd_shell to execute the dynamic SQL, and then doing the work in the command shell. Functions are allowed to run extended stored procedures. I highly, highly discourage you from going in that direction. It is way too complicated, it might stop working at some point, and it requires fiddling with permissions that might make your system more vulnerable.

我曾经看到一些奇怪的机制,其中函数可以调用xp_cmdshell,然后使用cmd_shell执行动态SQL,然后在命令shell中执行工作。允许函数运行扩展存储过程。我非常高兴,不鼓励你走向那个方向。它太复杂了,它可能会在某些时候停止工作,它需要摆弄可能使您的系统更容易受到攻击的权限。

EDIT:

You probably want a slightly more complicated expression (I didn't realize the parameter was on the right side as well as the left):

你可能想要一个稍微复杂的表达式(我没有意识到参数在右侧和左侧):

where ( (@parameterInTable = 'col1' and col1 = LEFT(@parameterFromUser , LEN(col1)) ) or
        (@parameterInTable = 'col2' and col2 = LEFT(@parameterFromUser , LEN(col2)) ) or
        . . .
      )

or using the case:

或使用案例:

where @parameterFromUser like
       (case when @parameterInTable = 'col1' then col1
             when @parameterInTable = 'col2' then col2
            . . .
        end) + '%';

#1