sql-server - 创建一个动态 SQL Where 子句,将 where 语句的两个部分都传入参数?即其中变量 1 = 变量 2
问题描述
我见过很多动态 where 子句的例子,但没有一个涵盖我遇到的问题。甚至可能吗?
Create Procedure ManualParse2
(
@Variable1 AS nvarchar(MAX)
,@Variable2 AS nvarchar(MAX)
)
AS
Select *
From Faculty
Where @Variable1
Case
When @Variable1 = 'Department' THEN Department
When @Variable1 = 'Name' THEN Name
When @Variable1 = 'Gender' THEN Gender
When @Variable1 = 'FacultyID' THEN FacultyID
END
= @Variable2
EXEC ManualParse2 @Variable1 = 'Gender', @Variable2 = 'Male'
解决方案
在字里行间阅读,但我怀疑你真正想要的是:
CREATE PROC dbo.ManualParse2 @ColumnName sysname, @ColumnValue nvarchar(50) AS --You aren't going to have a department with 1 billion characters in it
--sysname is the correct data type for object names (it's a synonym for nvarchar(128) NOT NULL)
BEGIN
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT *' + @CRLF + --This should reallty be a column list
N'FROM dbo.Faculty' + @CRLF +
N'WHERE ' + QUOTENAME(@ColumnName) + N' = @ColumnValue;';
--PRINT @SQL; --Unlikely to be needed for such a simple statement, but your debugging friend
EXEC sp_executesql @SQL, N'@ColumnValue nvarchar(50)', @ColumnValue;
END;
GO
推荐阅读
- android - 按钮样式背景颜色不起作用 - 仅限 XML
- python - 以编程方式从 Google 自定义搜索 JSON API 中排除/包含网站
- spring-boot - 将 Docker 参数传递给 Spring 引导属性
- mysql - 如果结果集中的所有值都为 NULL,则仅按特定字段排序查询
- python - 曲面上的动画散点图 (Plotly)
- istio - 如何为多集群 Istio Mesh 设置 PKI
- android - Android - java.lang.NoClassDefFoundError:解析失败:Lorg/slf4j/LoggerFactory;
- java - 如何使mysql中的列保存Java类
- google-sheets - Countifs 订单号 购物篮中有超过 1 件商品
- flutter - flutter_socket_io : 我的事件触发了,我的 JSON 没问题,但是没有发送消息