sql=" EXEC(SELECT COUNT(*) count FROM UDS_TASK)" rs.open(sql,db) count=parseInt(rs("count")) 参考一下吧!!!
--换种执行方式吧declare @count int declare @s nvarchar(4000) --注意类型 set @s='SELECT @count=COUNT(*) FROM UDS_TASK'--执行得到结果 exec sp_executesql @s,N'@count int out',@count out select 结果=@count
变量=SELECT top 1 COUNT(*) count FROM UDS_TASK)
/*本过程的作用是执行一个用户输入的语句,根据关键字段及关键字段当前值返回一定的记录条数,并将总记录条数通过过程返回*/ alter procedure SelectData @Select_SQL varchar(600),--需要执行的语句 @PrimaryKey varchar(20), --关键字段 @primaryValue int, --当前关键字值 @selectRecordCount int, --查询记录条数 @Dis bit --取数方向,1为向后取,否则为向前取 as declare @EXEC_SQL varchar(1000) declare @RecordCount int select @RecordCount = 0 begin select @EXEC_SQL = UPPER(LTrim(@Select_SQL)) select @EXEC_SQL = STUFF(@EXEC_SQL,8,CharIndex(' FROM ' ,@EXEC_SQL) - 8 , ' COUNT(*)') select @EXEC_SQL = STUFF(@EXEC_SQL,CharIndex(' ORDER BY ' , @EXEC_SQL) , len(@EXEC_SQL),'') select @RecordCount = exec (@EXEC_SQL) select @EXEC_SQL = UPPER(LTrim(@Select_SQL)) if((@Select_SQL <> '') or (@Select_SQL <> null)) and((@PrimaryKey <> '') or (@PrimaryKey <> null)) and((@PrimaryValue > 0) or (@PrimaryValue <> null)) and((@selectRecordCount > 0) or (@selectRecordCount <> null)) begin if ( CharIndex('SELECT ' ,@EXEC_SQL) > 0) select @EXEC_SQL = STUFF(@EXEC_SQL,8,0,'TOP '+Convert(varchar,@selectRecordCount) +' ') if (CharIndex(' WHERE ' ,@EXEC_SQL) > 0) begin select @EXEC_SQL = STUFF(@EXEC_SQL , CharIndex(' WHERE ' , @EXEC_SQL) + 7 , 0 , @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue) + ' AND ') end else begin select @EXEC_SQL = @EXEC_SQL + ' WHERE ' + @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue) end end exec(@EXEC_SQL) end
如果我执行该存储过程 SelectData 'select * from uds_task where projectid =0 order by id', 'id', 160, 10, 0 该过程构造的相应语名就是: SELECT COUNT(*) FROM UDS_TASK WHERE PROJECTID =0 SELECT TOP 10 * FROM UDS_TASK WHERE id < 160 AND PROJECTID =0 ORDER BY ID 而我怎么能取得第一个语句的执行结果?
/*本过程的作用是执行一个用户输入的语句,根据关键字段及关键字段当前值返回一定的记录条数,并将总记录条数通过过程返回*/ alter procedure SelectData @Select_SQL varchar(600),--需要执行的语句 @PrimaryKey varchar(20), --关键字段 @primaryValue int, --当前关键字值 @selectRecordCount int, --查询记录条数 @Dis bit --取数方向,1为向后取,否则为向前取 as declare @EXEC_SQL Nvarchar(4000) --改了变量类型定义 declare @RecordCount int select @RecordCount = 0 begin select @EXEC_SQL = UPPER(LTrim(@Select_SQL)) select @EXEC_SQL = STUFF(@EXEC_SQL,8,CharIndex(' FROM ' ,@EXEC_SQL) - 8 , ' @RecordCount=COUNT(*)') --改了处理语句 select @EXEC_SQL = STUFF(@EXEC_SQL,CharIndex(' ORDER BY ' , @EXEC_SQL) , len(@EXEC_SQL),'')-- select @RecordCount = exec (@EXEC_SQL) --改了执行方法 exec sp_executesql @EXEC_SQL,N'@RecordCount int out',@RecordCount out select @EXEC_SQL = UPPER(LTrim(@Select_SQL)) if((@Select_SQL <> '') or (@Select_SQL <> null)) and((@PrimaryKey <> '') or (@PrimaryKey <> null)) and((@PrimaryValue > 0) or (@PrimaryValue <> null)) and((@selectRecordCount > 0) or (@selectRecordCount <> null)) begin if ( CharIndex('SELECT ' ,@EXEC_SQL) > 0) select @EXEC_SQL = STUFF(@EXEC_SQL,8,0,'TOP '+Convert(varchar,@selectRecordCount) +' ') if (CharIndex(' WHERE ' ,@EXEC_SQL) > 0) begin select @EXEC_SQL = STUFF(@EXEC_SQL , CharIndex(' WHERE ' , @EXEC_SQL) + 7 , 0 , @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue) + ' AND ') end else begin select @EXEC_SQL = @EXEC_SQL + ' WHERE ' + @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue) end end exec(@EXEC_SQL) end
rs.open(sql,db)
count=parseInt(rs("count"))
参考一下吧!!!
declare @s nvarchar(4000) --注意类型
set @s='SELECT @count=COUNT(*) FROM UDS_TASK'--执行得到结果
exec sp_executesql @s,N'@count int out',@count out
select 结果=@count
alter procedure SelectData
@Select_SQL varchar(600),--需要执行的语句
@PrimaryKey varchar(20), --关键字段
@primaryValue int, --当前关键字值
@selectRecordCount int, --查询记录条数
@Dis bit --取数方向,1为向后取,否则为向前取
as
declare @EXEC_SQL varchar(1000)
declare @RecordCount int
select @RecordCount = 0
begin
select @EXEC_SQL = UPPER(LTrim(@Select_SQL))
select @EXEC_SQL = STUFF(@EXEC_SQL,8,CharIndex(' FROM ' ,@EXEC_SQL) - 8 , ' COUNT(*)')
select @EXEC_SQL = STUFF(@EXEC_SQL,CharIndex(' ORDER BY ' , @EXEC_SQL) , len(@EXEC_SQL),'')
select @RecordCount = exec (@EXEC_SQL)
select @EXEC_SQL = UPPER(LTrim(@Select_SQL))
if((@Select_SQL <> '') or (@Select_SQL <> null))
and((@PrimaryKey <> '') or (@PrimaryKey <> null))
and((@PrimaryValue > 0) or (@PrimaryValue <> null))
and((@selectRecordCount > 0) or (@selectRecordCount <> null))
begin
if ( CharIndex('SELECT ' ,@EXEC_SQL) > 0)
select @EXEC_SQL = STUFF(@EXEC_SQL,8,0,'TOP '+Convert(varchar,@selectRecordCount) +' ')
if (CharIndex(' WHERE ' ,@EXEC_SQL) > 0)
begin
select @EXEC_SQL = STUFF(@EXEC_SQL , CharIndex(' WHERE ' , @EXEC_SQL) + 7 , 0 , @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue) + ' AND ')
end
else
begin
select @EXEC_SQL = @EXEC_SQL + ' WHERE ' + @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue)
end
end
exec(@EXEC_SQL)
end
SelectData
'select * from uds_task where projectid =0 order by id',
'id',
160,
10,
0
该过程构造的相应语名就是:
SELECT COUNT(*) FROM UDS_TASK WHERE PROJECTID =0
SELECT TOP 10 * FROM UDS_TASK WHERE id < 160 AND PROJECTID =0 ORDER BY ID
而我怎么能取得第一个语句的执行结果?
alter procedure SelectData
@Select_SQL varchar(600),--需要执行的语句
@PrimaryKey varchar(20), --关键字段
@primaryValue int, --当前关键字值
@selectRecordCount int, --查询记录条数
@Dis bit --取数方向,1为向后取,否则为向前取
as
declare @EXEC_SQL Nvarchar(4000) --改了变量类型定义
declare @RecordCount int
select @RecordCount = 0
begin
select @EXEC_SQL = UPPER(LTrim(@Select_SQL))
select @EXEC_SQL = STUFF(@EXEC_SQL,8,CharIndex(' FROM ' ,@EXEC_SQL) - 8 , ' @RecordCount=COUNT(*)') --改了处理语句
select @EXEC_SQL = STUFF(@EXEC_SQL,CharIndex(' ORDER BY ' , @EXEC_SQL) , len(@EXEC_SQL),'')-- select @RecordCount = exec (@EXEC_SQL)
--改了执行方法
exec sp_executesql @EXEC_SQL,N'@RecordCount int out',@RecordCount out select @EXEC_SQL = UPPER(LTrim(@Select_SQL))
if((@Select_SQL <> '') or (@Select_SQL <> null))
and((@PrimaryKey <> '') or (@PrimaryKey <> null))
and((@PrimaryValue > 0) or (@PrimaryValue <> null))
and((@selectRecordCount > 0) or (@selectRecordCount <> null))
begin
if ( CharIndex('SELECT ' ,@EXEC_SQL) > 0)
select @EXEC_SQL = STUFF(@EXEC_SQL,8,0,'TOP '+Convert(varchar,@selectRecordCount) +' ')
if (CharIndex(' WHERE ' ,@EXEC_SQL) > 0)
begin
select @EXEC_SQL = STUFF(@EXEC_SQL , CharIndex(' WHERE ' , @EXEC_SQL) + 7 , 0 , @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue) + ' AND ')
end
else
begin
select @EXEC_SQL = @EXEC_SQL + ' WHERE ' + @PrimaryKey + case when @Dis = 1 then ' > ' else ' < ' end + convert(varchar,@PrimaryValue)
end
end exec(@EXEC_SQL)
end