• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

How to write a return value in a SP and how another SP get the return value from that SP?

Hi

There are 2 MSSQL stored procedures:
SP_A which will select a field from a table and return it.
SP_B which needs to declare a variable and the return value from SP_A will be assigned to it

How can I write those 2 stored procedures?
and, the SP_B need to write transaction to ensure commit otherwise rollback.

Is there any full source code example?
0
techques
Asked:
techques
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a procedure to "return" a single variable is written like this:
CREATE PROCEDURE SP_A 
( @result VARCHAR(100) OUTPUT
)
AS
BEGIN
  SELECT @result = somecolumn FROM sometable WHERE <some condition goes here>
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
procedure 2 would go like this:
CREATE PROCEDURE SP_B
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @variable VARCHAR(100)
  EXEC SP_A @variable OUTPUT 
  SELECT @variable as Result
END

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now