I had a SQL Server stored procedure doing a lookup using an XML-format view. It worked, but it was kind of slow and unnecessarily resource-intensive. I reformulated it for better performance, but the cost was making it dynamic SQL. A sample run is here:
Code:
Select SC1.SkupinaID From SouhrnyCloveks SC1
inner join TableOfCloveks TC1
on SC1.ClovekAutoID = TC1.ClovekAutoID
inner join SouhrnyCloveks SC2
on SC1.SkupinaID = SC2.SkupinaID
inner join TableOfCloveks TC2
on SC2.ClovekAutoID = TC2.ClovekAutoID
inner join SouhrnyCloveks SC3
on SC2.SkupinaID = SC3.SkupinaID
inner join TableOfCloveks TC3
on SC3.ClovekAutoID = TC3.ClovekAutoID
inner join SouhrnyCloveks SC4
on SC3.SkupinaID = SC4.SkupinaID
inner join TableOfCloveks TC4
on SC4.ClovekAutoID = TC4.ClovekAutoID
Where TC1.BPAuthorAbbreviation = '(' and SC1.Poradi = 1
AND TC2.BPAuthorAbbreviation = 'Crantz' and SC2.Poradi = 2
AND TC3.BPAuthorAbbreviation = ')' and SC3.Poradi = 3
AND TC4.BPAuthorAbbreviation = 'Vill.' and SC4.Poradi = 4
And (Select MAX(Poradi) from SouhrnyCloveks Where SkupinaID = SC1.SkupinaID) = 4
The number of joins (4 here) is not fixed. In theory, it can be any number. In practice, the max in this database is currently 12, and the overwhelming majority of cases are 4 or less. What I'm looking for is the group ID for the SET of records that meet the listed conditions. That ID will be unique - there is code to check that the identical set of records will not be saved twice, so these queries return either the one unique ID of the matching set of records, or nothing, if such a set does not exist.
I have read that passing a table as a parameter is not possible in Access, the way it is in vb.net. Is there a way to make a local Access table with these values and match the ENTIRE SET as a whole to a SQL Server table? I can build a normal join, but that will give me all records that match any of these conditions, not just the ones where all conditions match. For instance, there are 1338 records that match the first criterion, of TC1.BPAuthorAbbreviation = '(' and SC1.Poradi = 1.
i would have to pull all these superfluous records back across my ODBC link and assemble the sets, then check to see if any of the sets meets all of my conditions, just to get that one or none ID number. That's horribly complicated, inefficient, and quite likely very slow. (I'm not going to even bother trying to code it all to test the speed - that's just not how I want this to work.)
Is there a way to make the Access - SQL Server join match up only the entire set of records, or am I going to be stuck with building dynamic T-SQL to do this?