CODEWITHSUNDEEP
sql-servert-sqlado.net
John
John

Reputation: 2792

SQL Server While Loop return results as they are found

I have a SQL script that I've written that helps me search through a DB schema to find if certain columns are used (i.e. not null, not zero length string, etc) and what their most popular values are.

I'd really like to be able to return results as they're found in the loop since it could take a while to complete the entire search. Is there a way to return results in such a way that on the VB.NET side it will see the results as they are found when it tries to do SqlDataReader.Read?

Because right now, I'm storing the results in a temp table and returning the temp table at the end.

Thanks!

Upvotes: 0

Views: 262

Answers (1)

SqlRyan
SqlRyan

Reputation: 33924

Not when it's a single SQL script, no - the caller will wait for the full result set before moving on.

However, you could break it into a few steps, like this:

  • Make the initial lookup, where you list all the columns you might be interested in
  • Return these results to VB.NET
  • For each result in this set, run the rest of your process to get the values you're actually interested in. Loop through each result from the first step
  • As you receive each collection of data, you can choose to do something with it if you want. Spin off a new thread, for example, to do some additional processing.

So if it's a single T-SQL script, you're stuck running it and returning the results at one time - but if you're able to break it up and execute the loop inside .NET instead of inside SQL, you'll have access to the results at each step.

Upvotes: 1

Related Questions