存储过程定义如下:
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(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
如想实现 exec p_show "select * from aa ",10,2 的功能 在VB中应如何书写代码? 谢赐教
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(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
如想实现 exec p_show "select * from aa ",10,2 的功能 在VB中应如何书写代码? 谢赐教
cn.execute exec p_show 'select * from aa ',10,2
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim prm3 As ADODB.Parameter
' 使用一个参数打开命令对象。
Set cmdByRoyalty = New ADODB.Command
cmd.CommandText = "p_show"
cmd.CommandType = adCmdStoredProc
' 获取参数值并追加参数。
Set prm1 = cmd.CreateParameter("@QueryStr", _
adChar, adParamInput)
cmdB.Parameters.Append prm1
prm1.Value = " select * from a"
Set prm2 = cmd.CreateParameter("@QueryStr", _
adInteger, adParamInput)
cmd.Parameters.Append prm2
prm2.Value = 10 Set prm3 = cmd.CreateParameter("@QueryStr", _
adInteger, adParamInput)
cmdB.Parameters.Append prm3
prm1.Value = 2
'执行命令
Set cmd.ActiveConnection = cnn1
cmd.Execute
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim prm3 As ADODB.Parameter
' 使用一个参数打开命令对象。
Set cmdByRoyalty = New ADODB.Command
cmd.CommandText = "p_show"
cmd.CommandType = adCmdStoredProc
' 获取参数值并追加参数。
Set prm1 = cmd.CreateParameter("@QueryStr", _
adChar, adParamInput)
cmdB.Parameters.Append prm1
prm1.Value = " select * from a"
Set prm2 = cmd.CreateParameter("@PageSize", _
adInteger, adParamInput)
cmd.Parameters.Append prm2
prm2.Value = 10 Set prm3 = cmd.CreateParameter("@PageCurrent", _
adInteger, adParamInput)
cmdB.Parameters.Append prm3
prm1.Value = 2
'执行命令
Set cmd.ActiveConnection = cnn1
cmd.Execute
Set rs = cnDatabase.Execute("exec master..p_show 'select * from 数据库..a',10,2")