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. — Thom A
– Thom A ♦, Commented Jun 18, 2018 at 9:03
"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. — Thom A
– Thom A ♦, Commented Jun 18, 2018 at 9:05
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 — Thom A
– Thom A ♦, Commented Jun 18, 2018 at 9:09

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

0

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.

answered Jun 18, 2018 at 9:44

Vladimir Baranov

32.9k5 gold badges60 silver badges95 bronze badges

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

5 Comments

user2284877 Over a year ago

it works fine on a test tables. now i have to test on a real tables, t1 approx. 100, t2 approx millions

Vladimir Baranov Over a year ago

@user2284877, it would be interesting to know how it performs on the real tables compared to your VBA code.

user2284877 Over a year ago

join=cross over 47 minutes; (vba+where sp) = 1.03 minutes; table1 contains only one row: %keyword1% and %keyword2% and %keyword3%; all three methods returns (same)188 lines.

Vladimir Baranov Over a year ago

@user2284877, it should not take 47 minutes to read even 100M rows. The only thing that comes to mind is: what are the types of columns on the left and right of the LIKE? Are they the same in both tables?

user2284877 Over a year ago

t2 results from multiple joins, cols are concatenated and other colleagues run scripts on same table. but same conditions for select where in vba.

Collectives™ on Stack Overflow

Table values as parameters for SQL Server stored procedure

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related