Question

以下是样本查询。

CREATE PROCEDURE GetModel
(
    @brandids varchar(100), -- brandid="1,2,3"
    @bodystyleid varchar(100) -- bodystyleid="1,2,3"

)
AS
   select * from model
   where brandid in (@brandids) -- use a UDF to return table for comma delimited string
   and bodystyleid in (@bodystyleid)

我的要求是,如果或是空白的,查询应为此条件返回所有行。

请指导我如何做到这一点? 还建议如何写出这个查询, 以优化业绩。

Answer 1

您为此需要动态 SQL 或一个分割函数, 因为 IN (1, 23) 和 IN (1, 2, 3) 并不相同。

拆分函数 :

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( 
     SELECT Item = x.i.value( (./text())[1] ,  int ) FROM ( 
       SELECT [XML] = CONVERT(XML,  <i>  + REPLACE(@List, @Delimiter,  </i><i> ) 
       +  </i> ).query( . ) ) AS a CROSS APPLY [XML].nodes( i ) AS x(i)) AS y
     WHERE Item IS NOT NULL
   );

代码变得像:

SELECT m.col1, m.col2 FROM dbo.model AS m
LEFT OUTER JOIN dbo.SplitInts(NULLIF(@brandids,   ),  , ) AS br
ON m.brandid = COALESCE(br.Item, m.brandid)
LEFT OUTER JOIN dbo.SplitInts(NULLIF(@bodystyleid,   ),  , ) AS bs
ON m.bodystyleid = COALESCE(bs.Item, m.bodystyleid)
WHERE (NULLIF(@brandids,   ) IS NULL OR br.Item IS NOT NULL)
AND (NULLIF(@bodystyleid,   ) IS NULL OR bs.Item IS NOT NULL);

(注意,我在这里添加了很多 NULLIF 处理... 如果这些参数没有价值, 你应该通过 NULLL, 而不是“空白 ” ) 。)

动态SQL(SQL)由于测量参数而导致计划错误的可能性要小得多,它将是:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N SELECT columns FROM dbo.model
WHERE 1 = 1  
+ COALESCE(  AND brandid IN (  + @brandids +  ) ,   )
+ COALESCE(  AND bodystyleid IN (  + @bodystyleid +  ) ,   );

EXEC sp_executesql @sql;

当然,正如@JamieCee 所指出的,动态 SQL 可能很容易被注射,因为您会发现,如果在任何地方搜索动态 SQL, 任何地点都会发现。因此,如果你不信任输入, 您就会想防范潜在的注射袭击。就像您在应用程序代码中将临时 SQL 组合在一起一样。

当您移动到 SQL 服务器 2008 或更好时, 您应该查看 < a href=" http://msdn. microsoft. com/ en- us/library/b510489. aspx" rel= "nofollown noreferr" > canable-valued 参数 (

Answer 2

if(@brandids =    or @brandids is null)
Begin
     Set @brandids =  brandid 
End

if(@bodystyleid =    or @bodystyleid is null)
Begin
     Set @bodystyleid =  bodystyleid 
End

Exec( select * from model where brandid in (  + @brandids +  )
and bodystyleid in (  + @bodystyleid +  ) )

友情链接