• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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