SQL Server How to pass a list of ids from sproc to function
===

Unlike most situations that I've found on the internet when people need to pass a list of id's to a sproc from an application like c# then split the id's up so they can be used in the WHERE clause like WHERE IN (4,5,6,7,7,8)

I need to call a sql function from a sql stored procedure passing a list of id's. and I need to know the best way to do this, if possible it would be nice not to introduce a .Split function.

I need to add a constraint to the function so that it will not build a result set of all the Horizontals .

--This is the shared sql function

    ALTER FUNCTION [Storefront].[ufn_GetHorizontals] 
    (
    )
    RETURNS TABLE 
    AS
    RETURN 
    (
    	SELECT myColumList.* 'omitted'
    
    FROM   Storefront.Horizontal h	 	    with(nolock)                           JOIN
            Catelog.Part pt		     	    with(nolock)ON h.PartID = pt.ID		   JOIN
            Catelog.Brand bd		 	    with(nolock)ON pt.BrandID = bd.ID      JOIN
    		Storefront.Size sz				with(nolock)ON sz.ID = h.SizeID		   JOIN
            Storefront.Daylite dl		    with(nolock)ON sz.ID = dl.SizeID	   JOIN
            Storefront.Siteline sl		 	with(nolock)ON sl.ID = h.SitelineID    JOIN
            Storefront.Finish f		        with(nolock)ON f.ID = h.FinishID      LEFT JOIN
            Storefront.HorizontalGlass hg   with(nolock)ON hg.HorizontalID = h.ID LEFT JOIN
            Catelog.Glass g					with(nolock)ON hg.GlassID = g.ID
    )

--Here is a few examples that show the way THAT I DO NOT WANT to approach this, because it is obviously wrong.

-Example 1
SELECT  *

FROM   Storefront.Leaf l		 with(nolock)	                 JOIN
        Storefront.LeafHorizontal lh	 with(nolock)ON l.ID = lh.LeafID JOIN
        Storefront.ufn_GetHorizontals() h ON lh.HorizontalID = h.ID 
		 
WHERE l.ID = @LeafID;


- Example 2

    ALTER proc [Storefront].[proc_GetBayHorizontals]
     @BayID INT
    AS
    BEGIN
    SET NOCOUNT ON;
    
    		SELECT  *
    		
    		FROM   Storefront.Bay b		      with(nolock)		     JOIN
    		        Storefront.BayHorizontal bh   with(nolock)ON b.ID = bh.BayID JOIN
    		        Storefront.ufn_GetHorizontals() h ON bh.HorizontalID = h.ID 
    				 
   

     WHERE b.ID = @BayID;

----------------------------------------------------------------------------------

 I need something along the lines of this.



        ALTER FUNCTION [Storefront].[ufn_GetHorizontals] 
        (
          @MyListOfIdsFromTheStoredProcedure SOMEDATATYPE;
        )
        RETURNS TABLE 
        AS
        RETURN 
        (
        	SELECT myColumList.* 'omitted'
        
        FROM   Storefront.Horizontal h	 	    with(nolock)                           JOIN
                Catelog.Part pt		     	    with(nolock)ON h.PartID = pt.ID		   JOIN
                Catelog.Brand bd		 	    with(nolock)ON pt.BrandID = bd.ID      JOIN
        		Storefront.Size sz				with(nolock)ON sz.ID = h.SizeID		   JOIN
                Storefront.Daylite dl		    with(nolock)ON sz.ID = dl.SizeID	   JOIN
                Storefront.Siteline sl		 	with(nolock)ON sl.ID = h.SitelineID    JOIN
                Storefront.Finish f		        with(nolock)ON f.ID = h.FinishID      LEFT JOIN
                Storefront.HorizontalGlass hg   with(nolock)ON hg.HorizontalID = h.ID LEFT JOIN
                Catelog.Glass g					with(nolock)ON hg.GlassID = g.ID
      
    
      **********--->  WHERE h.ID IN(@MyListOfIdsFromTheStoredProcedure )
    
      )


***********-->Stored Procedure altered to a way like this.

		SELECT  *
		          FROM
           Storefront.ufn_GetHorizontals(SELECT HorizontalID FROM Storefront.BayHorizontal bh
                                             WHERE bh.BayID = @BayID)


Thank you ahead of time!