Bumping a Connect Issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Bumping a Connect Issue

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 09:27:39

Today I once again wished this Connect Issue was implemented in SQL Server
https://connect.microsoft.com/SQLServer/feedback/details/470881

It will really help speeding up procedures since you don't have to call them twice in certain cases. The Connect issue is about be able to store two ore more resultset in different tables within same EXEC statement.

If you care about the future of SQL Server, please go vote by clicking the Connect link and upvote the issue.

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 09:51:42

Voted up

But Instead of having

INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ)
EXEC usp_MyStoredProcedure @Param1, @Param2

It is better to identify the resultset and get it

EXEC usp_MyStoredProcedure [with RESULTSET n] @Param1, @Param2

Where n is an integer that specifies the resultset number

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 09:53:12

For example this?
[with RESULTSET (1, 2, 5, 6) ]

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 09:56:47

quote:
Originally posted by Peso

For example this?
[with RESULTSET (1, 2, 5, 6) ]

N 56°04'39.26"
E 12°55'05.63"

Yes

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 10:02:27

quote:
Originally posted by madhivanan

Yes

And how would the syntax be to store the resultset coming from the stored procedure?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 10:04:02

Something like this?

INSERT	Table1
	(
		Col1,
		Col2
	),
	Table2
	(
		ColX,
		ColY,
		ColZ
	)
EXEC	dbo.usp_MyStoredProcedure @Param1, @Param2
WITH	(RESULTSET = {2, 5})

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 10:12:29

quote:
Originally posted by Peso

Something like this?
INSERT Table1
(
Col1,
Col2
),
Table2
(
ColX,
ColY,
ColZ
)
EXEC dbo.usp_MyStoredProcedure @Param1, @Param2
WITH (RESULTSET = {2, 5})

N 56°04'39.26"
E 12°55'05.63"

This is perfect

Now I see that a Reporting tool can now very well identify the resultset

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-15 : 10:30:08

Voted up.

Not to threadjack, but I think there's a good overlap with the named/numbered result set enhancement and this suggestion too:

https://connect.microsoft.com/SQLServer/feedback/details/525653/stored-procedures-should-expose-detailed-contracts

It would be helpful in situations where result sets could have different structures, the contract could catch that as an error before runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources