A Stroed Procedure in side a StoredProcedure

• Anoop Saxena

  Mr or Mrs. 500

  Points: 547

  More actions

  December 4, 2008 at 2:50 am

  #196692

  How do we get value in 1x1 table returning from a Stroed Procedure in side a StoredProcedure without using temprery table in main stroed Procedure.

  thanks,

  Anoop

  Please help anybody

• Nandy

  Ten Centuries

  Points: 1016

  More actions

  December 4, 2008 at 2:58 am

  #907433

  Please can explain in detail and with an example.

• brainy

  SSCrazy

  Points: 2445

  More actions

  December 4, 2008 at 3:04 am

  #907437

  Yes Anoop, please explain it further.

• Anoop Saxena

  Mr or Mrs. 500

  Points: 547

  More actions

  December 4, 2008 at 3:07 am

  #907438

  suppose I have a sp which returns boolean value 0 or 1 how can we get that value in another procedure in which we calling first sp.

• vaseem-773819

  Ten Centuries

  Points: 1015

  More actions

  December 4, 2008 at 3:13 am

  #907440

  Hi Anoop

  You can take the output of calling sp using output parameter. Alternatively you can use udf also.

  Thanx

  Vaseem

• brainy

  SSCrazy

  Points: 2445

  More actions

  December 4, 2008 at 3:13 am

  #907442

  suppose I have a sp which returns boolean value 0 or 1 how can we get that value in another procedure in which we calling first sp.

  Refer following code:

  declare @a int

  execute @a = test

  print @a

  Here test is the sp which returns a value 0 or 1.

• Anoop Saxena

  Mr or Mrs. 500

  Points: 547

  More actions

  December 4, 2008 at 3:18 am

  #907444

  Sorry i have tryed it is not working.

• Anoop Saxena

  Mr or Mrs. 500

  Points: 547

  More actions

  December 4, 2008 at 3:20 am

  #907445

  Thanks vaseem

  grate job!

• brainy

  SSCrazy

  Points: 2445

  More actions

  December 4, 2008 at 3:30 am

  #907446

  Sorry i have tryed it is not working.

  Try this code

  create proc test

  as begin

  return 1

  end

  GO

  create proc test1

  as begin

  declare @a int

  execute @a = test

  print @a

  end

  execute test1

  Here in the above code test1 sp is calling test sp which returns 1.

  test1 sp will get the value 1 and it will print the value.

  This is how you can can the sp which is returning a value.

  You can also use a output variable to achieve the same thing.

• Anoop Saxena

  Mr or Mrs. 500

  Points: 547

  More actions

  December 4, 2008 at 5:17 am

  #907479

  thanks Gauthama

  

• Grant Fritchey

  SSC Guru

  Points: 398999

  More actions

  December 4, 2008 at 6:25 am

  #907509

  You can also use OUTPUT parameters.

  CREATE PROCEDURE proc1

  (@param1 INT

  ,@param2 INT OUTPUT)

  AS

  SET @param2 = @param1 ;

  GO

  CREATE PROCEDURE proc2

  AS

  DECLARE @myparam INT ;

  EXEC proc1 @param1 = 42, @param2 = @myparam OUTPUT ;

  SELECT @myparam ;

  GO

  EXEC proc2 ;

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning