更新时间:2023-11-27 09:19:52
您可以做的是使用动态 SQL 重写您的存储过程,并仅在定义参数时才包含 where
子句的部分内容,例如用
What you could do is to rewrite your stored proc using dynamic SQL and include parts of where
clause only if parameters are defined, e.g. replace the body with
declare @sql nvarchar(max)
set @sql = N'select * from Tbl_StampDutyException1 where 1 = 1'
if len(isnull(@exchange, '')) > 0
set @sql = @sql + N' and exchange = ' + cast(@exchange as nvarchar(100))
-- add all parameters; you need to cast them to nvarchar if they have other type
exec (@sql)
作为改进,您可以使用sp_executesql
来执行动态SQL.请参阅此处了解如何使用它.在这种情况下,代码将是:
As an improvement, you can use sp_executesql
to execute dynamic SQL. See here on how to use it. In this case, the code will be:
declare @sql nvarchar(max)
set @sql = N'select * from Tbl_StampDutyException1 where 1 = 1'
if len(isnull(@exchange, '')) > 0
set @sql = @sql + N' and exchange = @exchange'
-- add all parameters;
declare @params as nvarchar(max) = N'@Flag varchar(3),
@symbol varchar(13),
@exchange char(3),
@limit money,
@chargerate numeric(18,4),
@ChgType char(2),
@IsActive int,
@Mkrid varchar(11),
@statecode varchar(4)'
EXECUTE sp_executesql @sql, @params, @Flag, @symbol, @exchange, @limit, @chargerate, @ChgType, @IsActive, @Mkrid, @statecode
顺便说一句,不要在存储过程中使用select *
,这不是一个好习惯.列出要返回的所有列.否则,如果表定义发生变化,您将获得与之前不同的结果.
By the way, don't use select *
in stored procedures, it's not a good practice. List all the columns you want to return. Otherwise, if the table definition changes, you will get different result to what it was previously.