I work in a security and performance sensitive environment. Access to data is limited to server side SQL Stored procedures. Dynamically constructing "where" clauses with multiple Ids is not a possibility. In order to keep our procedures performing optimally we discourage repeat trips for requests for results with lists of Ids. Executing it in SQL looks like:
DECLARE @ids [dbo].[MyUserDefinedTableType]
INSERT INTO @ids (ID)
Values (1234),(2345),(9876)
EXEC [dbo].[myProc] @ids
(
@idList [dbo].[MyUserDefinedTableType] readonly
)
AS
select Name
from [dbo].Users u
join @idList p on p.Id = u.UserId
In ODBC this should be possible, but, Table Value Parameters can be of user defined type with multiple columns.
I work in a security and performance sensitive environment. Access to data is limited to server side SQL Stored procedures. Dynamically constructing "where" clauses with multiple Ids is not a possibility. In order to keep our procedures performing optimally we discourage repeat trips for requests for results with lists of Ids. Executing it in SQL looks like:
DECLARE @ids [dbo].[MyUserDefinedTableType] INSERT INTO @ids (ID) Values (1234),(2345),(9876)
EXEC [dbo].[myProc] @ids ( @idList [dbo].[MyUserDefinedTableType] readonly ) AS select Name from [dbo].Users u join @idList p on p.Id = u.UserId
In ODBC this should be possible, but, Table Value Parameters can be of user defined type with multiple columns.
ODBC 7.3 and above does support this data type: https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/use-table-valued-parameters-odbc?view=sql-server-ver15
https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/c264db71-c1ec-4fe8-b5ef-19d54b1e6566