我使用SQL Server 创建了下表
CREATE TABLE test
(
id_number NVARCHAR(50) NOT NULL,
number_of_products NVARCHAR(50) NOT NULL
);
INSERT INTO test (id_number, number_of_products)
VALUES (1000077004, 3),
(1000077005, 4),
(1000077006, 4),
(1000077007, 5),
(1000077008, 5),
(1000077009, 6);
我还创建了一个自定义过程，使用动态SQL来运行面向自定义的查询
程序：
CREATE PROCEDURE [dbo].[usp_dynamicquery1]
(@TableName NVARCHAR(50) = NULL,
@Field NVARCHAR(100) = NULL,
@Criterion NVARCHAR(100) = NULL,
@Parameter NVARCHAR(100) = NULL)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
DECLARE @ParameterDef NVARCHAR(500)
DECLARE @all VARCHAR(2) = '-1'
SET @ParameterDef = '@TableName NVARCHAR(50),
@Field NVARCHAR(100),
@Criterion NVARCHAR(100),
@Parameter NVARCHAR(100)'
SET @SQL = 'SELECT *
FROM TableName
WHERE Field Criterion Parameter'
SET @TableName = CASE
WHEN @TableName IS NOT NULL AND @TableName <> 0
THEN SET @SQL = @SQL+ ' AND TableName = @TableName'
ELSE '''' + @all + ''' = ''' + @all + ''''
END
SET @Field = CASE
WHEN @Field IS NOT NULL AND @Field <> 0
THEN SET @SQL = @SQL+ ' AND Field = @Field'
ELSE '''' + @all + ''' = ''' + @all + ''''
END
SET @Criterion = CASE
WHEN @Criterion = 'greater than'
THEN SET @SQL = @SQL+ ' AND Criterion = >'
WHEN @Criterion = 'greater than or equal'
THEN SET @SQL = @SQL+ ' AND Criterion = >='
WHEN @Criterion = 'less than'
THEN SET @SQL = @SQL+ ' AND Criterion = <'
WHEN @Criterion = 'less than or equal'
THEN SET @SQL = @SQL+ ' AND Criterion = <='
WHEN @Criterion = 'equal'
THEN SET @SQL = @SQL+ ' AND Criterion = ='
WHEN @Criterion = 'not equal'
THEN SET @SQL = @SQL+ ' AND Criterion = <>'
ELSE '''' + @all + ''' = ''' + @all + ''''
END
SET @Parameter = CASE
WHEN @Parameter IS NOT NULL AND @Parameter <> 0
THEN SET @SQL = @SQL+ ' AND Parameter = @Parameter'
ELSE '''' + @all + ''' = ''' + @all + ''''
END
EXEC sp_executesql @SQL,
@ParameterDef,
@TableName = @TableName, @Field = @Field, @Criterion = @Criterion, @Parameter = @Parameter
END
简单地说，我将解释我的程序的作用：
步骤1：将4个参数(
TableName, Field, Criterion, Parameter(作为输入步骤2：设置一个查询，根据样本标准(大于、小于等(选择所有值
步骤3：运行4个if-else语句来定义每个变量的值。基本上，如果用户给出一个值，则参数取该值，否则参数取值'-1'(@all(
步骤4：我执行命令
EXEC sp_executesql来动态完成我的查询
当我尝试创建过程时，我会遇到以下错误：
Msg 156，Level 15，State 1，Procedure usp_dynamicquery1，Line 45[Batch Start Line 0]
关键字"SET"附近的语法不正确。Msg 156，Level 15，State 1，Procedure usp_dynamicquery1，Line 46[Batch Start Line 0]
关键字'ELSE'附近的语法不正确。Msg 156，Level 15，State 1，Procedure usp_dynamicquery1，Line 51[Batch Start Line 0]
关键字"SET"附近的语法不正确。
运行以下查询时的预期结果：
[usp_dynamicquery1] @TableName='test', @Field='number_of_products', @Criterion='greater than', @Parameter = '4'
返回带number_of_products>4(因此是测试表的最后三行(。
你有正确的想法，正朝着正确的方向前进。我想我会帮你一把，让你知道我该怎么做。
这是我的存储过程版本：
CREATE OR ALTER PROCEDURE [dbo].[usp_dynamicquery1] (
@TableName NVARCHAR(50),
@Field NVARCHAR(100) = NULL,
@Criterion NVARCHAR(100) = NULL,
@Parameter NVARCHAR(100) = NULL,
@All VARCHAR(2) = '-1'
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@SQL NVARCHAR(MAX),
@SQL_WHERE NVARCHAR(MAX),
@ParameterDef NVARCHAR(500);
SET @ParameterDef = '@Parameter NVARCHAR(100)'
SET @SQL = 'SELECT * FROM ' + @TableName;
SET @SQL_WHERE = '';
/* BUILD THE WHERE CLAUSE IF @Field IS PRESENT */
IF NULLIF ( @Field, '' ) IS NOT NULL BEGIN
-- Field.
SET @SQL_WHERE = ' WHERE ' + @Field;
-- Field Comparison.
SET @SQL_WHERE += CASE @Criterion
WHEN 'greater than' THEN ' >'
WHEN 'greater than or equal' THEN ' >='
WHEN 'less than' THEN ' <'
WHEN 'less than or equal' THEN ' <='
WHEN 'not equal' THEN ' <>'
ELSE ' ='
END;
-- Field Parameter.
SET @SQL_WHERE += ' @Parameter';
-- Set @Parameter value.
SET @Parameter =
CASE WHEN NULLIF ( @Parameter, '' ) IS NOT NULL
THEN @Parameter
ELSE @All
END;
END
-- Finish SQL statement.
SET @SQL = @SQL + ISNULL ( @SQL_WHERE, '' ) + ';';
-- Execute the dynamic statement.
EXEC sp_executesql @SQL, @ParameterDef, @Parameter = @Parameter;
END
GO
一些值得注意的快速事项：
@TableName必须为必填项，否则，您打算查询什么？只有当存在
@Field时，才会构造WHERE子句。将
@ParameterDef变量缩短为仅包括@Parameter。t-sql的其余部分是在调用sp_execute_sql之前形成的。我离开了@Parameter，这样你就不必担心值周围的单个刻度，等等。将
@All作为DEFAULTed参数(-1(添加到SP，如果需要，可以更改该值。