Click here to Skip to main content
15,892,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
HI all ,
i know how to call one sp from another one by using exec method. now i want to use variables that are return by that sp.


suppose i am working on sp to insert data but before that i want to check certain flag in the database, and for that i call other sp. now in insert procedure how to use values of exec procedure, i want to get value which is return by the sp and set to some variable so i can check for duplication.


Thanks
Posted

SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE   [dbo].[validateQuery](
   @as_validationparam1 VARCHAR(20),
   @as_validationparam2 VARCHAR(20),
   @as_validationparam3 VARCHAR(20),
   @as_validatedResult varchar(100) out
)
as
BEGIN
  ----------do validation stuff
   
   set @as_validatedResult  ='result after validation'
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE   [dbo].[callingQuery](
   @as_callingQueryparam1 VARCHAR(20),
   @as_callingQueryparam2 VARCHAR(20)
)
as
DECLARE @ds_result varchar(100)
BEGIN
  
   -----------call for validation
   EXEC	validateQuery 'validationparam1Value','validationparam2Value','validationparam3Value' , @ds_result OUT
END
 
Share this answer
 
v2
Using an output parameter, this can be done quite easily.
E.g.
SQL
DECLARE @count INT
EXEC GetCount 'abc', @count OUTPUT
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900