Table values as parameters for SQL Server stored procedure

Question

I have table1 :col1, col2, col3 and table2: col1, col2, col3
My goal is to get all records

where     
t2.col1 like t1.col1 and    
t2.col2 like t1.col2 and    
t2.col3 like t1.col3

........................................

One variant is the inner join method

select * from t2 inner join t1 on    
t2.col1 like t1.col1  and  
t2.col2 like t1.col2  and  
t2.col3 like t1.col3

........................................

Another variant is a stored procedure based on the 'where' clause:

select *  from t2     
where t2.col1 like parameter1  and      
t2.col2 like parameter2  and     
t2.col3 like parameter3

Then I call the procedure in VBA and I use a for next loop to go through all values/parameters from an excel table1
........................................
Execution time for the join method is slower(~20, 30%) than vba+sp method, but unfortunately, for a big set of parameters, excel freeze.
........................................
Is possible to apply loop method and go thru table1 values, as parameters for the stored procedure, inside sql server, in a sql script, no vba or c++ or perl etc. ?

I am a user with no access to db/tables design.

Thank you

Do the columns col1,col2, and col3 have wildcards in them? If not, why are you using LIKE? Also, what exactly is your question here? What you ask at the bottom is very vague, but to answer your question "Is possible to apply loop method and go thru table1 values, as parameters for the stored procedure, inside sql server, in a sql script, no vba or c++ or perl etc. ? ": Yes, you can loop inside SQL Server; however, generally it's a bad idea, a data set approached will almost always be faster. — Larnu
"values are between %" What does that mean? That doesn't make any sense. Unless you're saying that Table1 has values in the columns like 'Green%Yellow%Red', however, that would mean your expression would be something like 'Green%Yellow%Red' LIKE 'Green', which would evaluate to false. If my guess is also true, however, you have a major problem with your design model. — Larnu
Ok, Both table1 is on the left hand side of your expression, so '%value1%' LIKE 'value1' will evaluate to false. I think you need to post some sample data here, expected results, and expand on your question here. We have too little to go on right now. How to post data for a T-SQL Question — Larnu

Vladimir Baranov Vladimir Baranov · Accepted Answer · 2018-06-18T09:44:13

First of all, your two queries in the question are not equivalent:

select * from t2 inner join t1 on    
t1.col1 like t2.col1  and  
t1.col2 like t2.col2  and  
t1.col3 like t2.col3

Here you have t1 like t2

select *  from t2     
where t2.col1 like parameter1  and      
t2.col2 like parameter2  and     
t2.col3 like parameter3

Here it is other way around t2 like t1.

End result would be different.

Based on the sample data it looks like it should be t2 like t1.

You can try to re-write the query using CROSS APPLY instead of JOIN, but it is unlikely to make any difference in performance.

SELECT *
FROM
    t1
    CROSS APPLY
    (
        SELECT
        FROM t2
        WHERE
            t2.col1 like t1.col1
            and t2.col2 like t1.col2
            and t2.col3 like t1.col3
    ) AS A
;

This query structure mimics your stored procedure approach where for each row from t1 you select a set of rows from t2.

Table values as parameters for SQL Server stored procedure

1 Answers