tsql - using internal stored procedure as parameter is where clause

Question

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!~

Answer 1

You can't use the results of a stored procedure directly in a select statement You'll either have to output the results into a temp table, or make the sproc into a table valued function to do what you doing.

I think this is valid, but I'm doing this from memory

create table #tmp (blah, blah)

Insert into #tmp
exec dbo.sprocName

Answer 2

I would use an inline table valued user defined function. Or simply code it inline is no re-use is required

CREATE dbo.GetSuitableCategories
           (
--parameters
          )
RETURNS TABLE
AS
RETURN (
    select * from categoryTable categories
    where categories.gender IN (3, @gender)
)

Some points though:

• I assume categoryTable has no gender = 0
• Do you have 3 genders in your categoryTable? :-)
• Why do pass in 3 parameters but only use 1? See below please
• Does @sex map to @gender?

If you have extra processing on the 3 parameters, then you'll need a multi statement table valued fucntions but beware these can be slow