返回记录总数的存储过程

[netwar]

用户传来一个表名，返回这个表的记录总数，这个存储过程怎么改进呢、谢谢

alter proc testa(
@stable varchar(250),
@recordcount varchar(20) output
)
as declare @str varchar(500)
select @str='select @recordcount=count(*) from '+@stable
exec(@str)

[gahade]

alter proc testa(
@stable varchar(250),
@recordcount int output
)
as declare @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

[gahade]

--测试代码
declare @i int
exec testa 'sysobjects',@i output
print @i

[hellowork]

alter proc testa(
@stable varchar(250),
@recordcount varchar(20) output
)
as declare @str Nvarchar(500)
select @str=N'select @recordcount=CONVERT(varchar,count(*)) from '+@stable
exec sp_executesql @str,N'@recordcount varchar(20) output',@recordcount output

[leo_lesley]

alter proc testa(
@stable varchar(250),
@recordcount int output
)
as declare @str nvarchar(500)
select @str=N'select @recordcount=count(*) from '+@stable
Execute sp_executesql @str,N'@recordcount int OUTPUT',@recordcount OUTPUT

[leo_lesley]

看见的时候是0，转眼见5

[gahade]

前3楼都只差3秒,好险!

[mengmou]

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

[netwar]

谢谢两位，问题解决，顺便问一下，exec sp_executesql 这个地方与exe有什么区别呢？

[gahade]

sp_executesql可以包含参数,exec不行