Having problems calling a procedure which returns output variables from another proc.

[harsha_forum]

Hi All,

Iam having a proc which has dynamic sql inside that.

ex-


Use Northwind

CREATE PROC TEST(
@DATABASENAME VARCHAR(30),
@COUNTOUT INT OUTPUT
)
AS
BEGIN
DECLARE @v_SQL nVARCHAR(4000)
DECLARE @COUNT INT


SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'

EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT
SET @COUNTOUT=@COUNT

RETURN @countout
END



the above sp returns the @countout param.The authors table exists in the pubs database .
Now i want to call this sp from another sp.

ex-

CREATE PROC CALL
AS
BEGIN
DECLARE @SQL NVARCHAR(100)
DECLARE @COUNTOUT INT
SET @SQL= 'EXEC TEST ''pubs'''
print @SQL
EXEC SP_EXECUTESQL @SQL,n'@COUNTOUT int output'


END

But iam unable to do this ..Can anyone help me in this ???