CREATE PROCEDURE randomid
@tiku int,
@driveType int
AS
declare @sqlStr varchar(500)
if @tiku<>1
begin
set @[email protected]+'UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE [email protected] ORDER BY newid()) x'
end
if @driveType<>1
begin
set @[email protected]+'UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE [email protected] 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 @[email protected]+'UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE [email protected] and tiku=1 ORDER BY newid()) x'
end
这段好像没有执行？错在哪呢
------解决方案--------------------
- SQL code
CREATE PROCEDURE randomid@tiku int,@driveType intASdeclare @sqlStr varchar(500) set @sqlStr = '' --> 赋值，不赋值为NULLif @tiku <>1begin--> UNIOn all 前面要有一个空格；变量 @tiku 抽出来set @[email protected]+' UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE tiku='+ltrim(@tiku)+' ORDER BY newid()) x' --> endif @driveType <>1begin--> UNIOn all 前面要有一个空格；变量 @driveType 抽出来set @[email protected]+' UNIOn all SELECT id FROM (SELECT top 1000 id,newid() x FROM paper WHERE driveType='+ltrim(@driveType)+' and tiku=1 ORDER BY newid()) x'endexec('SELECT id FROM (SELECT top 1320 id, newid() x FROM paper WHERE driveType=1 and tiku=1 ORDER BY newid() ) x' + @sqlStr)GO