recursive_stored_procedure.sql

ALTER PROCEDURE [dbo].[Providers_Get_All_Spacial_Terms_New]
	...

AS
BEGIN

   IF Object_id('tempdb..#T') IS NOT NULL
	   BEGIN
			INSERT INTO #T ....
	   END
	ELSE
	   BEGIN
			SELECT ...
			INTO #T
	   END


	IF...
		BEGIN

			SELECT * from #T ...
			DROP TABLE #T
		END
	ELSE
	BEGIN
			exec Providers_Get_All_Spacial_Terms_New @ParentID,@ServiceGroupID,@ServiceTypeID,@ServiceDate,@PlanCurrency
	END

END