CODEWITHSUNDEEP
sqlsql-server
user2284877
user2284877

Reputation: 138

Table values as parameters for SQL Server stored procedure

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

enter image description here

Upvotes: 1

Views: 188

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

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.

Upvotes: 0

Related Questions