set nocount on

create table Category
 (	CategoryID int,
	CategoryTypeID int)

create table CategoryRelations
 (	RecordID int,
	ParentID int,
	ChildID int)
go

insert into category 
select 1,1	--category 1, type 1
union
select 2,1	--category 2, type 1
union
select 3,1	--category 3, type 1
union
select 4,1	--category 4, type 1
union
select 5,1	--category 5, type 1
union
select 6,2	--category 6, type 2

insert into CategoryRelations
select 1,1,2	-- record1, which is parent 1, child 2
union
select 2,1,3	-- record2, which is parent 1, child 3
union
select 2,1,4	-- record2, which is parent 1, child 4
union
select 2,5,1	-- record2, which is parent 5, child 1
union
select 2,5,6	-- record2, which is parent 5, child 6
go

CREATE PROCEDURE spv_Products_GetCategoryRelations @CategoryID int,@CategoryTypeID int
AS
--note: I haven't added error handling (@@Error), or parametre checking (making sure you receive a value)
--imho you should add these...
select 
  CR.ChildId     --all childid's
from
  CategoryRelations CR
  inner join
  Category C
    on c.categoryid=cr.childid
where
	cr.parentID 		= @categoryID
and c.CategoryTypeID 	= @CategoryTypeID
--order by c.orderingNumber asc There is no ordering number in the example structure.
order by CR.ChildId
go

declare
	@CategoryID int,
	@CategoryTypeID int

set @CategoryID 	= 1  --we want children of 1
set @CategoryTypeID = 1  -- for categorytype 1

exec spv_Products_GetCategoryRelations @CategoryID, @CategoryTypeID

drop table category
drop table CategoryRelations
drop procedure spv_Products_GetCategoryRelations

---results
-- ChildId     
-- ----------- 
-- 2
-- 3
-- 4