请高手帮忙改一下返回参数的存储过程,谢谢先!
CREATE PROCEDURE MoreData
@TableName varchar (8),
@iCount varchar(4) OUTPUT
AS
DECLARE @SQL VARCHAR(1000)
SET @SQL= 'SELECT id,sTitle FROM '+@TableName
EXEC(@SQL)
SET @SQL= 'SELECT '+@iCount+ ' =COUNT(*) FROM '+@TableName
EXEC(@SQL)
GO
===================================================================
执行时没有出现异常,第一行查询返回的结果正常,但是第二行的返回参数(总记录数)为空!请帮我改下,谢谢!!!
[解决办法]
CREATE PROCEDURE MoreData
@TableName varchar (8),
@iCount int OUTPUT
AS
DECLARE @SQL VARCHAR(1000)
SET @SQL= 'SELECT id,sTitle FROM '+@TableName
EXEC(@SQL)
Declare @temp int
execute sp_executesql @SQL,N '@temp int out ',@temp out
Set @iCount = @temp
GO
[解决办法]
CREATE PROCEDURE MoreData
@TableName varchar (8),
@iCount varchar(4) OUTPUT
AS
DECLARE @SQL VARCHAR(1000)
SET @SQL= 'SELECT id,sTitle FROM '+@TableName
EXEC(@SQL)
SET @SQL= 'SELECT '+@iCount+ '=COUNT(*) FROM '+@TableName
EXEC(@SQL)
GO
declare @iCount varchar(4)
exec moredata 'authors ', @iCount output
print @iCount
