34,594
社区成员
发帖
与我相关
我的任务
分享谁帮我解释下这个存储过程相关的疑问
CREATE proc getdataset
@TableList Varchar(200)='*',--搜索表的字段,比如：’id，datatime，job‘，用逗号隔开
@TableName Varchar(30), --搜索的表名
@SelectWhere Varchar(500)='',--搜索条件，这里不用写where，比如：job=’teacher‘and class='2'
@SelectOrderId Varchar(20),--表主键字段名。比如：id
@SelectOrder Varchar(200)='', --排序，可以使用多字段排序但主键字段必需在最前面.也可以不写，比如：order by class asc
@intPageNo int=1, --页号
@intPageSize int=10 ,--每页显示数
@RecordCount int OUTPUT --总记录数(存储过程输出参数)
as
declare @TmpSelect NVarchar(600)
declare @Tmp NVarchar(600)
set nocount on--关闭计数
set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' '+@SelectWhere
execute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句，output出总记录数
@RecordCount OUTPUT
if (@RecordCount = 0) --如果没有贴子，则返回零
return 0
/*判断页数是否正确*/
if (@intPageNo - 1) * @intPageSize > @RecordCount --页号大于总页数，返回错误
return (-1)
set nocount off--打开计数
if @SelectWhere != ''
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectWhere +' '+@SelectOrder+') and '+@SelectWhere +' '+@SelectOrder
end
else
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)
GO
问题
1：这个存储过程效率高吗？我测试37W数据的情况下翻页速度一般
2：
set nocount on
set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' '+@SelectWhere
execute sp_executesql
@TmpSelect, N'@RecordCount int OUTPUT' ,
@RecordCount OUTPUT
if (@RecordCount = 0) return 0
if (@intPageNo - 1) * @intPageSize > @RecordCount return (-1)
set nocount off--打开计数
set nocount on， set nocount off。关闭-打开 受SQL语句影响的行
set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' '+@SelectWhere
这句属于影响影响行的SQL语句吗？
set nocount on， set nocount off 一会关闭一会打开有什么用尼？
3：
sp_executesql 这个存储过程怎么用的尼。查了些资料米看懂。就上面的例子而言带有三个参数
@TmpSelect,
N'@RecordCount int OUTPUT' ,
@RecordCount OUTPUT
朋友帮我解释下。还有N’是什么意思？
4：
execute sp_executesql @TmpSelect
return(@@rowcount)
整个存储过程执行完输出受影响的行数。
整个过程有三处返回值。还有一个OUTPUT 输出变量。共四个值了有点迷糊return 返回的值在.cs文件里面怎么获取尼？
获取记录总数的是return(@@rowcount) 还是 @RecordCount OUTPUT尼。看了与此存储过程相对应的.CS。是用的@RecordCount 返回的记录总数。
...全文
请发表友善的回复…
发表回复
-狙击手- 2009-02-02
- 打赏
- 举报
[Quote=引用 8 楼 cngothic 的回复:]
楼上大哥的意思是
table where id > 1000
这样吗。5楼大哥给的这个存储过程在05SQL库上性能高吗。我我技术很差看不出来好坏
4楼大哥回的
DECLARE @RecordCount 數據類型
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
现又提示错误：
消息 119，级别 15，状态 1，第 2 行
必须传递参数 3，并以 '@name = value' 的形式传递后续的参数。一旦使用了 '@name …
[/Quote]
table where id > 1000
--
select * from table where id > 1000
楼上大哥的意思是
table where id > 1000
这样吗。5楼大哥给的这个存储过程在05SQL库上性能高吗。我我技术很差看不出来好坏
4楼大哥回的
DECLARE @RecordCount 數據類型
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
现又提示错误：
消息 119，级别 15，状态 1，第 2 行
必须传递参数 3，并以 '@name = value' 的形式传递后续的参数。一旦使用了 '@name …
[/Quote]
table where id > 1000
--
select * from table where id > 1000
cngothic 2009-02-02
- 打赏
- 举报
楼上大哥的意思是
table where id > 1000
这样吗。5楼大哥给的这个存储过程在05SQL库上性能高吗。我我技术很差看不出来好坏
4楼大哥回的
DECLARE @RecordCount 數據類型
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
现又提示错误：
消息 119，级别 15，状态 1，第 2 行
必须传递参数 3，并以 '@name = value' 的形式传递后续的参数。一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递。
table where id > 1000
这样吗。5楼大哥给的这个存储过程在05SQL库上性能高吗。我我技术很差看不出来好坏
4楼大哥回的
DECLARE @RecordCount 數據類型
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
现又提示错误：
消息 119，级别 15，状态 1，第 2 行
必须传递参数 3，并以 '@name = value' 的形式传递后续的参数。一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递。
-狙击手- 2009-02-02
- 打赏
- 举报
谢谢两位朋友
楼上朋友给的存储过程没有
where 。。。。 的变量 这个存储过程无法用做于带条件的情况吧
-----------------------------------
@QueryStr nvarchar(4000), --表名、视图名、查询语句
可用用查询SQL取代你的表名呀，查询语句就可以带where
楼上朋友给的存储过程没有
where 。。。。 的变量 这个存储过程无法用做于带条件的情况吧
-----------------------------------
@QueryStr nvarchar(4000), --表名、视图名、查询语句
可用用查询SQL取代你的表名呀，查询语句就可以带where
cngothic 2009-02-02
- 打赏
- 举报
谢谢两位朋友
楼上朋友给的存储过程没有
where 。。。。 的变量 这个存储过程无法用做于带条件的情况吧
楼上朋友给的存储过程没有
where 。。。。 的变量 这个存储过程无法用做于带条件的情况吧
中国风 2009-02-02
- 打赏
- 举报
05用ROW_NUMBER()
http://msdn.microsoft.com/en-us/library/ms345144(SQL.90).aspx
2000可用
/*--用存储过程实现的分页程序
显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
--邹建 2003.09(引用请保留此信息)--*/
/*--调用示例
exec p_show '地区资料'
exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO
CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
水族杰纶 2009-02-02
- 打赏
- 举报
[Quote=引用 3 楼 cngothic 的回复:]
5：追加一个问题
我在查询分析器中
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
提示错误：必须声明标量变量 "@RecordCount"。
应该怎么修改？
[/Quote]
5：追加一个问题
我在查询分析器中
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
提示错误：必须声明标量变量 "@RecordCount"。
应该怎么修改？
[/Quote]
DECLARE @RecordCount 數據類型
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT cngothic 2009-02-02
- 打赏
- 举报
5：追加一个问题
我在查询分析器中
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
提示错误：必须声明标量变量 "@RecordCount"。
应该怎么修改？
我在查询分析器中
exec getdataset
@TableName = 'a11',
@SelectOrderId = 'ID',
@RecordCount OUTPUT
提示错误：必须声明标量变量 "@RecordCount"。
应该怎么修改？
水族杰纶 2009-02-02
- 打赏
- 举报
--朋友帮我解释下。还有N’是什么意思？
--N表示UNICODE 如 NVARCHAR() NTEXT水族杰纶 2009-02-02
- 打赏
- 举报
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:字段名，表名，数据库名之类作为变量时，必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格
当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确
3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中？
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num 
