tsql - using internal stored procedure as parameter is where clause
- by vondip
Hi all,
I'm tryng to build a stored procedure that makes use of another stored proceudre. Taking its result and using it as part of its where clause, from some reason I receive an error:
Invalid object name 'dbo.GetSuitableCategories'.
Here is a copy of the code:
select distinct top 6 * from
(
SELECT TOP 100 *
FROM [dbo].[products] products
where products.categoryId in
(select top 10 categories.categoryid from
[dbo].[GetSuitableCategories]
(
-- @Age
-- ,@Sex
-- ,@Event
1,
1,
1
) categories
ORDER BY NEWID()
)
--and products.Price <=@priceRange
ORDER BY NEWID()
)as d
union
select * from
(
select TOP 1 * FROM [dbo].[products] competingproducts
where competingproducts.categoryId =-2
--and competingproducts.Price <=@priceRange
ORDER BY NEWID()
) as d
and here is [dbo].[GetSuitableCategories] :
if (@gender =0)
begin
select * from categoryTable categories
where categories.gender =3
end
else
begin
select * from categoryTable categories
where categories.gender = @gender
or categories.gender =3
end
Thank you very much!~
