How2 read the Nth result set from a sproc in SQL?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How2 read the Nth result set from a sproc in SQL??

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2009-05-13 : 16:35:57

Team,

Business Need 1: Read from a canned sproc that produces multiple result sets.

Business Need 2: Pipe the results into separate tables.

Business Need 3: This must be called from Crystal Reports.

Constraint: I cannot use SSIS.

Development Environment: SQL 2005, Visual Studio 2005

The canned sproc has nine result sets and two parameters. Which architecture should I choose?

Trial 1) I tried using nine TVFs written as nine CLRs, but . . .

I was not allowed to run a canned sproc that had "SET NOCOUNT ON" imbedded within it.
I was not allowed to say "SET NOCOUNT ON;EXEC CannedSproc" because you cannot use EXEC from within a CLR.

Trial 2) I cannot find a way to read the Nth result set of a canned sproc from within SQL 2005.

Trial 3) I tried using an ADO.NET datasource for Crystal, but I can't use code to load the dataset with data. It must be called from Crystal directly.

I'm Stuck--

Please HELP!

~ Shaun Merrill
Seattle, WA

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-13 : 16:44:16

Without know the stored procedure, I need to assume.

but you can always use the row_number function


Select * from
(
Select Row_Number() over (order by col1) as RowID,*
from
my table aa
) a
where a.RowID = 3213 -- Your nth result

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SMerrill
Posting Yak Master

206 Posts

Posted - 2009-05-13 : 16:54:43

No, I mean it has nine select statements in the canned sproc.

For example, if you ran
sp_help 'sys.sp_help'
you would see two result sets. The second resultset has columns "Parameter_name, Type, Length, "etc. Try to pump the second result set into a table, and you'll get my problem. You may use T-SQL and/or .NET to do this.

Now in .NET, it is easy using the .NextResult method ...

m_SqlDataReader = .ExecuteReader() 'RUN ALL NINE SELECT STATEMENTS
Dim iResult As Integer = 0
Do
   iResult += 1
Loop Until Not m_SqlDataReader.NextResult Or iResult = m_ResultSetID 'ADVANCE TO NEXT SELECT STATEMENT

But not so easy in T-SQL.
~ Shaun Merrill
Seattle, WA

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-13 : 17:55:28

How about a CLR stored procedure that executes the canned sproc and:

1. assembles a .Net DataSet object of all the results that Crystal can use (if Crystal supports raw DataSets)
2. returns an XML document containing all nine tables that Crystal can shred as needed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources