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

Question

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!

Answer 1

The inline function GetBayIDs() takes a single integer and creates the XML document of IDs that is passed into your function and subsequently handed off to the Split() function to filter the dataset in your function. It's hard to test without having Storefront.BayHorizontal, but I think this is pretty close...

create function dbo.GetBayIDs(@BayID int) returns xml
as
begin
  declare @xml varchar(max), @i int

  set @xml = '<root>'

  declare cr cursor local for
    SELECT HorizontalID
    FROM   Storefront.BayHorizontal bh
    WHERE  bh.BayID = @BayID

  open cr
  fetch next from cr into @i

  while @@fetch_status = 0
  begin
    set @xml = @xml + '<r>' + cast(@i as varchar(10)) + '</r>'
    fetch next from cr into @i
  end

  close cr
  deallocate cr

  set @xml = @xml + '</root>'

  return @xml
end
go

create function dbo.Split(@ids xml) returns @rtn table (id varchar(10))
as
begin
  insert into @rtn
  select r.value('.', 'varchar(10)') as [id]
  from @ids.nodes('//root/r') as records(r)

  return
end
go

ALTER FUNCTION [Storefront].[ufn_GetHorizontals] 
    (
      @idlist xml
    )
    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      LEFT JOIN
            dbo.Split(@idlist) spl                      ON h.id = spl.id
  )
go

declare @BayID int = 123

SELECT *
FROM   Storefront.ufn_GetHorizontals(dbo.GetBayIDs(@BayID))

go
drop function dbo.Split
go
drop function dbo.GetIDs