返回记录总数的存储过程
用户传来一个表名，返回这个表的记录总数，这个存储过程怎么改进呢、谢谢
alter proc testa(
@stable varchar(250),
@recordcount varchar(20) output
)
asdeclare @str varchar(500)
select @str= 'select @recordcount=count(*) from '+@stable
exec(@str)
[解决办法]
alter proc testa(
@stable varchar(250),
@recordcount int output
)
asdeclare @str nvarchar(500)
select @str= 'select @recordcount=count(*) from '+@stable
exec sp_executesql @str,N '@recordcount int output ',@recordcount output
[解决办法]
alter proc testa(
@stable varchar(250),
@recordcount int output
)
as
begin
declare @str nvarchar(500)
select @str=N 'select @recordcount=count(*) from '+@stable
exec sp_executesql @str,N '@recordcount int out ', @recordcount out
return
end
go
[解决办法]
alter proc testa(
@stable varchar(250),
@recordcount varchar(20) output
)
asdeclare @str Nvarchar(500)
select @str=N 'select @recordcount=CONVERT(varchar,count(*)) from '+@stable
exec sp_executesql @str,N '@recordcount varchar(20) output ',@recordcount output
[解决办法]
--测试代码
declare @i int
exec testa 'sysobjects ',@i output
print @i
[解决办法]
alter proc testa(
@stable varchar(250),
@recordcount int output
)
asdeclare @str nvarchar(500)
select @str=N 'select @recordcount=count(*) from '+@stable
Execute sp_executesql @str,N '@recordcount int OUTPUT ',@recordcount OUTPUT
[解决办法]
use pubs
go
alter proc testa(
@stable varchar(250),
@recordcount int output
)
as
declare @str nvarchar(4000)
select @str=N 'select @recordcount=count(*) from '+@stable
exec sp_executesql @str,N '@recordcount int output ',@recordcount output
go
declare @i int
exec testa 'authors ',@i output
select @i
[解决办法]
sp_executesql可以包含参数,exec不行
