Call Stored procedure from within another stored procedure, return values

This quick how-to shows an example of having two stored procedures that one stored procedure executes the other stored procedure passing values and returning a value to the calling parent stored proc. This would be handy passing in a value to dynamically populate a lookup table and stored that Id as a foreign key in a datatable. . This example asssumes you would have a parent table and a child/lookup table related back to the parent table.[code]'This stored procedure is called by a bu ...

Contributed by
Rating: 3 stars3 stars3 stars3 stars3 stars / 84
May 07, 2003
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement
This quick how-to shows an example of having two stored procedures that one stored procedure executes the other stored procedure passing values and returning a value to the calling parent stored proc. This would be handy passing in a value to dynamically populate a lookup table and stored that Id as a foreign key in a datatable. . This example asssumes you would have a parent table and a child/lookup table related back to the parent table.


'This stored procedure is called by a business component or webpage 
'
passing into two parametersexecutes sp_ChildStoredProc and passes on value
'
The @Id OUTPUT returns the value.  

CREATE PROCEDURE sp_ParentStoredProc
(
@col1 varchar(20)
@ValuePassed varchar(50),
)
AS

declare @Id int
EXEC sp_ReturnValue @ValuePassed, @Id OUTPUT

INSERT INTO SomeTable
(col1, col2)
VALUES
(@col1, @Id)
RETURN
GO 

'This stored procedure is executed in the sp_ParentStoredProc above and passes'a varchar variable piece of data. The spChildStoredProc will insert this piece of data'into a lookup table if it doesn't exist, return the Identity column using Scope_Identity() function or'will return the identity value to the parent stored proc if the piece of data passed in already exists in the lookup table.


CREATE PROCEDURE sp_ChildStoredProc

(
@
ValuePassedIn varchar(50),
@
Id smallint OUTPUT
)
 AS

If 
exists (SELECT column1 FROM LookUpTable WHERE column1=@ValuePassedIn)
BEGIN
SET 
@Id = (SELECT column1 
   FROM LookUpTable 
   WHERE column1
=@ValuePassedIn)
END 
ELSE
BEGIN
INSERT INTO LookUpTable
(column1
VALUES(@ValuePassedIn)
    
    
SET @Id Scope_Identity()
END
GO

blog comments powered by Disqus
ASP.NET CODE ARTICLES

- How to Use the ListBox Control in ASP.NET 2.0
- How to Load XML Documents in ASP.NET 2.0
- DataGrid Code
- ASP.NET Guestbook
- User Controls and Client Side Scripting
- ASP.NET Programming with Microsoft's AS...
- ASP.NET Basics (part 3): Hard Choices
- ASP.NET Basics (part 2): Not My Type
- ASP.NET Basics (part 1): Nothing But .Net
- Directory Tree Browser
- How to get the confirmation of Yes/No from a...
- Complete example using custom errors and wri...
- Paging Certain # records per page .NET style
- General Methods of formatting and Subtractin...
- .NET LinkButton web control

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 6 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials