Passing multi-value parameters to MS Sql Server

1. Passing multi-value parameters to MS Sql Server

2. Why would we do this? • SELECT rows based on a range of values • Filter reports based on user selection • Deleting a series of rows • Bulk INSERT/UPDATE

3. Approaches • Looping • Pass delimited string and parse with a Split implementation • Pass XML fragment (SQL 2005+) • User Defined Table Types (SQL 2008+)

4. Looping var results = new List<T>(); foreach (string val in valuesToLookFor) { // Do data access stuff results.Add(someQueryResult) }

5. Delimited and Split List<T> results; string values = “a, b, c, d, e”; // Data access setup // Pass in values as param to sproc results = //Read results from DataReader

6. Delimited and Split (cont.) CREATE PROCEDURE dbo.p_Table_Read ( @Values VARCHAR(50) ) AS SELECT t.Column1, t.Column2 FROM dbo.Table t JOIN dbo.Split(@Values) s ON s.value = t.Column4

7. XML Fragment List<T> results; string values = “<Root><Row val=‘a’ /><Row val=‘b’ /><Row val=‘c’ /></Root>”; // Data access setup // Pass in values as param to sproc results = //Read results from DataReader

8. XML Fragment (cont.) CREATE PROCEDURE dbo.p_Table_Read ( @Values XML ) AS SELECT t.Column1, t.Column2 FROM dbo.Table t JOIN @Values.nodes(‘/Root/Row’) v(n) ON v.n.value(‘@val’, VARCHAR(25)) = t.Column4

9. User Defined Table Type CREATE TYPE dbo.MyUddt AS TABLE ( Value VARCHAR(100) NOT NULL )

10. User Defined Table Type (cont.) List<T> results; DataTablevalues = myValuesTable; // Data access setup // Pass in values as param to sproc param = new SqlParameter(“@Values”, SqlDbType.Structured).Value = values; param.TypeName = “dbo.MyUddt”; results = //Read results from DataReader

11. User Defined Table Type (cont.) CREATE PROCEDURE dbo.p_Table_Read ( @Values dbo.MyUddt READONLY ) AS SELECT t.Column1, t.Column2 FROM dbo.Table t JOIN @Valuesv ON v.value= t.Column4

12. Performance

13. Test Setup • My machine: • Intel i5-2500 @ 3.30 GHz (quad-core) • 8GB RAM • 160GB Intel SSD (SSDSA2BW160G3H) • Windows 7 64-bit w/ SP1 • SQL 2008 R2 w/ SP1 • Test runs: • Each test is primed • Row counts = 1, 10, 50, 100, 500, 1000, 5000, 10000, 5000

14. Test Setup (cont.) • Values for each run were determined at random • All tests run against local copy of Tlink Dental from Dev • All sprocs used are identical with exception of input parameter • Each sproc does an index seek and key lookup against a NCI on dbo.Patient • Entity Framework 4.3 was used for data access

15. Overall Results

16. Overall without Loops

17. Split Functions

18. Links/References • A split strings comparison article: link • The SQL 8k CSV Splitter: link (registration req.) • The SQL CLR splitter: link