sql stored procedure that selects from array/list/xml input parameter

Question

I’m looking for idea’s/solution to the following problem.

Require a stored procedure that accepts a single parameter (array, list, collection, xml, etc…). For each item in the array/list/collection/xml it will execute a query like this:

Select * from <some table> t
Where t.Name = Parameter.Name and t.datetime = Parameter.datetime

The results would all be returned as a single result set.

As an example this procedure would be called with the following details passed in as a parameter value:

Books 09/09/2009 11:23:23

Books 09/09/2009 11:23:23 Authors 04:22:04

Books 09/09/2009 11:23:23 Authors 04:22:04 Catalog 9:45:11

Looking for a variety of ideas to perform some stress/timings on it.

Thanks

Duplicate (several times): stackoverflow.com/questions/1922191/… — Mitch Wheat
– Mitch Wheat, Commented Dec 21, 2009 at 14:33
Is there a reason you want these statements generated inside of a stored procedure rather than compiled code? — Zack
– Zack, Commented Dec 21, 2009 at 14:46

Mahesh Kumar · Accepted Answer · 2015-06-04 10:18:45Z

1

CREATE TYPE id_list AS TABLE (
    id int NOT NULL PRIMARY KEY
);
GO

CREATE PROCEDURE [dbo].[tvp_test] (
      @param1           INT
    , @customer_list    id_list READONLY
)
AS
BEGIN
    SELECT @param1 AS param1;

    -- join, filter, do whatever you want with this table 
    -- (other than modify it)
    SELECT *
    FROM @customer_list;
END;
GO

DECLARE @customer_list id_list;

INSERT INTO @customer_list (
    id
)
VALUES (1), (2), (3), (4), (5), (6), (7);

EXECUTE [dbo].[tvp_test]
      @param1 = 5
    , @customer_list = @customer_list
;
GO

DROP PROCEDURE dbo.tvp_test;
DROP TYPE id_list;
GO

answered Jun 4, 2015 at 10:18

Mahesh Kumar

92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql stored procedure that selects from array/list/xml input parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related