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 bu ...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 business component or webpage
'passing into two parameters, executes 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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP.NET Code Articles
More By aspfree  developerWorks - FREE Tools!
|
Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
FREE! Go There Now!
| | | |
Download a free trial version of IBM Rational Software Analyzer Developer Edition V7.0 to identify bug defects earlier in the software development cycle. Rational Software Analyzer is an extensible software development solution that reduces the expense of bug-fixes by enabling static analysis code reviews and bug identification very early in the development cycle.
FREE! Go There Now!
| | | |
Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
FREE! Go There Now!
| | | |
XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
FREE! Go There Now!
| | | |
Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
| | | |
This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly.
FREE! Go There Now!
| | | |
In this webcast, you'll get an introduction to the eXtreme Transaction Processing (XTP) features of WebSphere Extended Deployment and the common architectural traits required by XTP applications. See how WebSphere Extended Deployment's ObjectGrid feature provides a state-of-the-art infrastructure for hosting XTP applications.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
