Execute a SP from Within a SP and return a value

[da75]

Hi all

I have just started working with stored procedures and i have a SP called SMS_RatioBarReport which calls the SP SMS_Ratio and then uses this value and inserts it into a temporary table.

this is the SP I call

**********************************************************

CREATE PROCEDURE SMS_Ratio
	@Name VARCHAR(30),
	@Network VARCHAR(30),
	@Count INT = NULL OUTPUT
AS

DECLARE @SQLQuery VARCHAR(500)
DECLARE @Temp INT

	SELECT @Count = (SELECT COUNT(Ratio) FROM RatioReport WHERE [Name]= @Name  and Network= @Network and Ratio=0 )

RETURN

GO

**********************************************************

I think the problem occurs when i am calling it this is the
SP that calls it

**********************************************************

CREATE PROCEDURE SMS_RatioBarReport 

AS

DECLARE @SQLString VARCHAR(8000)
DECLARE @IP_ID INT
DECLARE @Name VARCHAR(30)	
DECLARE @Network VARCHAR(20)
DECLARE @CountNetwork INT
DECLARE @CountRatio INT
DECLARE @CountBarred INT

	CREATE TABLE #TempReport
	(
	IP_ID INT,
	[Name] VARCHAR(20),
	Network VARCHAR(20),
	CountNetwork INT
	)

	CREATE TABLE #TempFinal
	(
	IP_ID INT,
	[Name] VARCHAR(20),
	Network VARCHAR(20),
	CountNetwork INT,
	CountRatio INT,
	CountBarred INT
	)

	SET NOCOUNT ON

SET @SQLString = 'INSERT INTO #TempReport SELECT    SIM_RT.IP_ID, OutStations.Name, Port_Map.Network, COUNT(Port_Map.Network) AS CountNetwork FROM SIM_RT INNER JOIN Port_Map ON SIM_RT.IP_ID = Port_Map.IP_ID AND SIM_RT.Port_ID = Port_Map.Port_Id INNER JOIN OutStations ON SIM_RT.IP_ID = OutStations.IP_ID WHERE(dbo.SIM_RT.SIM_Live = 1) GROUP BY SIM_RT.IP_ID,[Name],Network ORDER BY [Name],Network 

	EXEC (@SQLString)

	DECLARE TempRecord CURSOR
   	FOR SELECT * FROM #TempReport
		OPEN TempRecord
		FETCH NEXT FROM TempRecord
		INTO @IP_ID, @Name, @Network, @CountNetwork

	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN

	EXECUTE @CountRatio = SMS_Ratio Name, @Network, @Count = @CountRatio OUTPUT


	SET @CountBarred=2

SELECT @SQLString = 'INSERT INTO #TempFinal ( IP_ID, [Name],Network,CountNetwork,CountRatio,CountBarred) VALUES(' + CAST(@IP_ID AS VARCHAR) + ', ' + char(39) + @Name + char(39)  +',' + char(39) + @Network + char(39) + ','  + CAST(@CountNetwork AS VARCHAR) + ',' + CAST (@CountRatio AS VARCHAR)+ ',  ' + CAST (@CountBarred AS VARCHAR) + ')'
		
	EXEC (@SQLString)
		END

		FETCH NEXT FROM TempRecord
		INTO @IP_ID, @Name, @Network, @CountNetwork
	END

	CLOSE TempRecord
	DEALLOCATE TempRecord

	SET NOCOUNT OFF

	SELECT * FROM #TempFinal

	DROP TABLE #TempReport
	DROP TABLE #TempFinal
GO

**********************************************************

When i try to Execute SMS_RatioBarReport i get an error saying internal SQL server error, i have tried differant ways of writing the SMS_Ratio and also calling the SP and i am getting confused. Hope you can help Thank you in advance

Danielle