在 SQL(动态查询)中过滤数据的最佳方法是什么? [英] What is the best approach to filter data in SQL (Dynamic Query)?

问题描述

我有一个存储过程，它应该从根据提供的参数过滤的表中返回结果集.

I have a stored procedure which is supposed to return a result set from a table filtered according to parameters provided.

更新

alter procedure  Proc_CheckExchange     
@Flag varchar(3),      
@symbol varchar(13)=null,        
@exchange char(3)=null,        
@limit money=null,        
@chargerate numeric(18,4)=null,        
@ChgType char(2)=null,        
@IsActive int=null,        
@Mkrid varchar(11)=null,        
@statecode varchar(4) =null    


as         
 declare @sql nvarchar(max)  

set @sql = N'select * from Tbl_StampDutyException1 where 1 = 1'  
if(@Flag='CHK')      
begin   


if len(isnull(@exchange, '')) > 0  
  set  @sql = @sql + N'and exchange=@exchange'+ cast(@exchange as nvarchar(100))  

if len(isnull(@symbol, '')) > 0  
 set   @sql = @sql + N'and symbol=@symbol'+ cast(@symbol as nvarchar(100))  

if len(isnull(@limit, '')) > 0  
 set   @sql = @sql + N'and limit=@limit'+ cast(@limit as nvarchar(100))  

if len(isnull(@chargerate, '')) > 0  
  set  @sql = @sql + N'and chargerate=@chargerate'+ cast(@chargerate as nvarchar(100))  

if len(isnull(@ChgType, '')) > 0  
 set   @sql = @sql + N'and ChgType=@ChgType'+ cast(@ChgType as nvarchar(100))  

if len(isnull(@IsActive, '')) > 0  
  set  @sql = @sql + N'and IsActive=@IsActive'+ cast(@IsActive as nvarchar(100))  

    if len(isnull(@statecode, '')) > 0  
 set   @sql = @sql + N'and statecode=@statecode'+ cast(@statecode as nvarchar(100))  



exec (@sql)  
end  

if (@Flag='ALL')      
begin    
 select * from Tbl_StampDutyException1   
end

更新 1

    alter procedure  Proc_CheckExchange     
@Flag varchar(3),      
@symbol varchar(13)=null,        
@exchange char(3)=null,        
@limit money=null,        
@chargerate numeric(18,4)=null,        
@ChgType char(2)=null,        
@IsActive int=null,        
@Mkrid varchar(11)=null,        
@statecode int =null    


as         
 declare @sql nvarchar(max)  

set @sql = N'select * from Tbl_StampDutyException1 where 1 = 1'  
if(@Flag='CHK')      
begin   

  if len(isnull(@exchange, '')) > 0
    set @sql = @sql + N' and exchange = @exchange'

   if len(isnull(@limit, '')) > 0
    set @sql = @sql + N' and limit = @limit'

    if len(isnull(@chargerate, '')) > 0
    set @sql = @sql + N' and chargerate = @chargerate'

    if len(isnull(@ChgType, '')) > 0
    set @sql = @sql + N' and ChgType = @ChgType'

    if len(isnull(@IsActive, '')) > 0
    set @sql = @sql + N' and IsActive = @IsActive'

    if len(isnull(@statecode, '')) > 0
    set @sql = @sql + N' and statecode = @statecode'

    if len(isnull(@symbol, '')) > 0
    set @sql = @sql + N' and symbol = @symbol'

 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)'
print @sql
--EXECUTE sp_executesql @sql, @params, @Flag, @symbol, @exchange, @limit, @chargerate, @ChgType,    @IsActive, @Mkrid, @statecode

    end

我正在尝试创建一个存储过程，其中 WHERE 子句中的条件与传递给存储过程的条件一样多.我希望我清楚我要实现的目标.我收到错误 Error 将数据类型 varchar 转换为数字.

I am trying to create a stored procedure in which there will be as many conditions in WHERE clause as passed to the stored procedure. I hope I am clear about what I am trying to achieve. I am getting error Error converting data type varchar to numeric.

推荐答案

您可以做的是使用动态 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.

这篇关于在 SQL(动态查询)中过滤数据的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！