Hi Skippy
I just try to convert the same into a function, So now u can use it any where with in the sql query.
USE [DKOWare]
GO
CREATE FUNCTION [dbo].[GetNumberOfChildrenTrue] (@PKParentCriterium int, @PKPerson int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
declare @sql nvarchar(max)
declare @test nvarchar(max)
declare @R_Int int
-- 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 @R_Int = 'select ' + @test + ' FROM PersonsCriteria WITH (NOLOCK) WHERE PKPersons = ' + @PKPerson
RETURN(@R_Int);
END;
SELECT [dbo].[GetNumberOfChildrenTrue](1,1) AS 'Child_Val';
NOTE : I just write code, you may be get some syntax error, So be ready for that.
Happy New Year ... Wish u good luck :)
Regards,
Vijay