create proc query_records
@dowhat tinyint=3,
@id int
as
if @dowhat=1
begin
select n_title,n_note,uptime from tablename where n_id=@id
return
end
if @dowhat=2
begin
select n_title,uptime from tablename where n_class=150
return
end
if @dowhat=3
begin
select top 6 * from tablename where n_class=150
union all
select * from tablename where n_class=151
return
end
print '@dowhat is not in (1,2,3)'
go
@dowhat tinyint=3,
@id int
as
if @dowhat=1
begin
select n_title,n_note,uptime from tablename where n_id=@id
return
end
if @dowhat=2
begin
select n_title,uptime from tablename where n_class=150
return
end
if @dowhat=3
begin
select top 6 * from tablename where n_class=150
union all
select * from tablename where n_class=151
return
end
print '@dowhat is not in (1,2,3)'
go
@dowhat int,
@id int =null
as
if @dowhat=1
select n_title,n_note,uptime from t where n_id=@id
if @dowhat =2
select n_title,uptime from t where n_class=150
if @dowhat=3
select top 6 * from t where n_class=150
union
select top 6 * from t where n_class=151go
@dowhat int,
@id int =null
as
if @dowhat=1
select n_title,n_note,uptime from t where n_id=@id
if @dowhat =2
select n_title,uptime from t where n_class=150
if @dowhat=3
select top 6 * from t where n_class=150
union
select top 6 * from t where n_class=151go
set nocount on再试下!!
你只是查询数据,可以这样:rs.open strsql,conn,1,1
涉及到多个记录集的时,用set nocount on(结束处写回-set nocount off),或者用set rs=rs.movenextrecordset()
select top 6 * from t where n_class=150 order by n_id desc
union
select top 6 * from t where n_class=151 order by n_id desc有没有别的什么好办法?
我需要的是n_class=150和n_class=151各6条数据,并且是按照n_id的大小排序的
select identity(int,1,1) x, * into #tmp2 from t where n_class=150 order by n_id descselect * from #tmp1 where x<=6 union select * from #tmp2 where x<=6
服务器: 消息 8108,级别 16,状态 1,行 1
无法使用 SELECT INTO 语句向表 '#tmp1' 中添加标识列,该表中已有继承了标识属性的列 'n_ID'。
我将代码改为:
select top 6 * into #tmp1 from t where n_class=150 order by n_id desc
select top 6 * into #tmp2 from t where n_class=151 order by n_id descselect * from #tmp1
union all
select * from #tmp2
第一次执行正确,再次执行的时候提示出错:
数据库中已存在名为 '#tmp1' 的对象。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tablename]
GO
很不好意思,我看不太明白你给的代码,能具体解释一下这段代码吗?
select top 6 * into #tmp1 from t where n_class=150 order by n_id desc
select top 6 * into #tmp2 from t where n_class=151 order by n_id descselect * from #tmp1
union all
select * from #tmp2returndrop table #tmp1
drop table #tmp2能够在查询分析器里正确执行,但是我在asp里调用的时候好象不能返回值,一用到
rs.movenext的时候就提示出错信息:ADODB.Recordset (0x800A0CC1)
create proc proc_test
as
begin
set nocount on
select top 6 * into #tmp1 from t where n_class=150 order by n_id desc
select top 6 * into #tmp2 from t where n_class=151 order by n_id descselect * from #tmp1
union all
select * from #tmp2set nocount off
end