what does it mean by a single and multiple result-set in a query -- in relation to lookup behavior?

Question

Hello There,

I found the note-point while going through lookup-documentation and couldn't understand the difference between a one result and more than one result set -->

'When you use query or stored procedure to lookup data, make sure to return one and exact one result set. Otherwise, Lookup activity fails.'

Am relatively a new learner and progressing through ADF and databricks practice.

Would anyone help me understand the concern?

Thank you for giving your valuable time and your kind support!

Here is the link for the documentation: [https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

Answer 1

In Azure Data Factory, when you execute a query or stored procedure, the query or stored procedure can return one or more result sets.

A result set is a table of data returned by a query or stored procedure. In the case of one result set, the query or stored procedure returns one table of data. In the case of multiple result sets, the query or stored procedure returns multiple tables of data.

When you are working with one result set, you can access the data by using the first index of the result set array, for example, resultSet[0].

When you are working with multiple result sets, you need to access each result set by its index, for example, resultSet[0], resultSet[1], etc.

It's important to note that when you are using a stored procedure, you need to check how many result sets are returned by that procedure, and handle them accordingly in your pipeline.

In Azure Data Factory, the Execute SQL task is used to execute queries and stored procedures, and it supports both one result set and multiple result sets. The output of this task can be used as an input to other tasks in the pipeline, such as the Copy Data task, to further process the data.

It's important to mention that not all Data sources support multiple result sets, so when you are working with those sources, the result sets returned will always be one.