exec 里申明的变量到外面是不能用的,改:
exec ('declare cro_fastread cursor scroll for select id ' + @str_sql + ' order by id desc
open cro_fastread
close cro_fastread
deallocate cro_fastread
')
exec ('declare cro_fastread cursor scroll for select id ' + @str_sql + ' order by id desc
open cro_fastread
close cro_fastread
deallocate cro_fastread
')
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@int_userstate'。
exec sp_user_sel 1,0,'','',0,1,20,@int_recordcount
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@int_userstate'。
但是执行的时候declare @int_recordcount int
exec sp_user_sel 1,0,'name','a',0,1,20,@int_recordcount
select @int_recordcount返回的的数据对记录对了
可是返回的int_recordcount 记录是 NULL 我晕 ....if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_USER_SEL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_USER_SEL]
GOCREATE PROCEDURE [dbo].[SP_USER_SEL]
(
@int_all int=1, --是否显示全部注册用户(无主域名ID限制)
@int_domainid int=0, --主域名ID
@str_colname nvarchar(100), --列名
@str_colvalue nvarchar(100), --列值
@int_userstate int=0, --用户状态
@int_pagenow int=1,
@int_pagesize int=50,
@int_recordcount int=0 output --就是得出总数
)
AS
SET nocount on
declare @str_allid nvarchar(500) --获得全部记录数的SQL
declare @str_sql nvarchar(500) --执行SQL
declare @int_allid int --记录总数
declare @str_cursor nvarchar(500) --游标SQL
declare @int_beginid int,@int_endid int
declare @int_pagebegin int, @int_pageend intif len(@int_all)=0
begin
if len(@str_colname)>0 and len(@str_colvalue)>0
begin
set @str_sql=' from dbo.[user] where domain_id=' + str(@int_domainid) + ' and user_state=' + str(@int_userstate) + ' and ' + @str_colname + ' like ''%' + @str_colvalue + '%'' '
end
else
begin
set @str_sql=' from dbo.[user] where domain_id=' + str(@int_domainid) +' and user_state=' + str(@int_userstate) + ' '
end
end
else
begin
if len(@str_colname)>0 and len(@str_colvalue)>0
begin
set @str_sql=' from dbo.[user] where user_state=' + str(@int_userstate) + ' and '+ @str_colname +' like ''%'+@str_colvalue +'%'''
end
else
begin
set @str_sql=' from dbo.[user] where user_state=' + str(@int_userstate) + ' '
end
endset @str_allid='select @int_allid=count(id) '+@str_sqlexec sp_executesql @str_allid,N'@int_allid int out',@int_allid outselect @int_recordcount=@int_allid --得出总数
set @str_cursor='declare cro_fastread cursor scroll for select id ' + @str_sql + ' order by id desc'exec sp_executesql @str_cursoropen cro_fastreadselect @int_beginid=(@int_pagenow-1)*@int_pagesize+1
select @int_endid = @int_beginid+@int_pagesize-1fetch absolute @int_beginid from cro_fastread into @int_pagebegin if @int_endid>@int_allid
fetch last from cro_fastread into @int_pageend
else
fetch absolute @int_endid from cro_fastread into @int_pageend
set @str_sql='select * ' + @str_sql + 'and id between '+str(@int_pageend)+' and '+str(@int_pagebegin)+' order by id desc'exec sp_executesql @str_sqlclose cro_fastread
deallocate cro_fastreadreturn
GO
exec sp_user_sel 1,0,'name','a',0,1,20,@int_recordcount out ---*** 没有指定为输出
select @int_recordcount