How to fill a variable with result of a dynamic sql?

Hi all,

I made a stored procedure in which I create a sql (based on 2 given variables). This sql returns a single value (of type int).
Now I want to use this value in other sqls by calling this procedure, but I don't seem to get any return, although the dynamic sql always has a result.
This is my stored procedure:

USE [DKOWare]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetNumberOfChildrenTrue]
	@PKParentCriterium int, @PKPerson int, @return int output
AS
BEGIN
	SET NOCOUNT ON;

declare @sql nvarchar(max)
declare @test nvarchar(max)
-- create the sql
-- this sql returns the column names of the fields to check
-- all those fields are bit values
-- I need the amount of fields set to TRUE
set @test = (select '+ convert(int, isnull([' + convert(nvarchar,pk) + '],0)) ' from OTCriterium WITH (NOLOCK) WHERE ParentPK = @PKParentCriterium FOR XML PATH)
set @test = replace(@test, '<row>','')
set @test = replace(@test, '</row>','')
set @test = substring(@test,2,1000)

set @sql = 'select ' + @test + ' FROM PersonsCriteria WITH (NOLOCK) WHERE PKPersons = ' + @PKPerson

EXEC sp_executesql @sql

END

I assume I need to convert the procedure into a function, but don't know how to do that. Any ideas or solutions? Thanks in advance!

Tags: SQL, SQL Server 2005, SQL Server, SQL Server 2008