日期:2014-05-18  浏览次数:20340 次

多条件查询 存储过程
CREATE PROCEDURE randomid
@tiku int,
@driveType int
AS
declare @sqlStr varchar(500) 

if @tiku<>1
begin
set @sqlStr=@sqlStr+'UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE tiku=@tiku ORDER BY newid()) x'
end
if @driveType<>1
begin
set @sqlStr=@sqlStr+'UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE driveType=@driveType and tiku=1 ORDER BY newid()) x'
end
exec('SELECT id FROM (SELECT top 1320 id, newid() x FROM paper WHERE driveType=1 and tiku=1 ORDER BY newid() ) x' + @sqlStr)
GO

execute randomid 1,10和execute randomid 1,7得到的结果一样,问题出在哪?
if @driveType<>1
begin
set @sqlStr=@sqlStr+'UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE driveType=@driveType and tiku=1 ORDER BY newid()) x'
end
这段好像没有执行?错在哪呢

------解决方案--------------------
SQL code
CREATE PROCEDURE randomid
@tiku int,
@driveType int
AS
declare @sqlStr varchar(500) 

set @sqlStr = '' --> 赋值,不赋值为NULL

if @tiku <>1
begin
--> UNIOn all 前面要有一个空格;变量 @tiku 抽出来
set @sqlStr=@sqlStr+' UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE tiku='+ltrim(@tiku)+' ORDER BY newid()) x' --> 
end
if @driveType <>1
begin
--> UNIOn all 前面要有一个空格;变量 @driveType 抽出来
set @sqlStr=@sqlStr+' UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE driveType='+ltrim(@driveType)+' and tiku=1 ORDER BY newid()) x'
end
exec('SELECT id FROM (SELECT top 1320 id, newid() x FROM paper WHERE driveType=1 and tiku=1 ORDER BY newid() ) x' + @sqlStr)
GO