Master Detail stored proc - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Master Detail stored proc

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2015-01-23 : 06:58:05

hi

I have 2 stored procs. Stored Proc A return productid based on country. Stored Proc B reutrn record based on the productid returned by Stored Proc A

I used these 2 procs in a nested repeater in asp.net and the display the result as expected.

But i noticed something in profiler. There were multiple call on Stored Proc B for each productid return by Stored Proc A.

How should i combine the 2 procs as one. These are the stored proc:

Create Proc A
@Country varchar(20)
as
Select ProductID, ProductName from Product where Country = @Country

Create Proc B
@ProductID int
as
Select SalesID, SalesDate, SalesAmount from Sales
where ProductID = @ProductID

Thanks a lot in advance.

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 08:52:04

You could use Cursor or while loop to call recursively. inside the loop can write the Procedure logic

Regards
Viggneshwar A

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2015-01-23 : 09:59:32

But i need to return 2 result sets with just one stored pro. How should i use while loop? Thanks in advance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-23 : 10:47:08

You could combine the two procs by adding both parameters to the new proc and executing both selects. However, you'll need to change your application to handle the two result sets.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources