How to Set variable based on result of procedure

• abhijit jadhav

  Valued Member

  Points: 53

  More actions

  January 19, 2007 at 5:52 am

  #115411

  How I can Set variable based on result of procedure

  create procedure p1 as select clientname from clientmast where .....some condition

  The above query returns only one record set. I would like to use the output string of procedure ie., return code of procedure p1 in the other procedure say p2 as variable for further computation. like below.....(I am very bad in coding)

  create procedure p2

  as

  declare @myclname char(10)

  set @myclname=exec p1

  Thanks in advance....

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  January 19, 2007 at 6:03 am

  #684255

  This is very similar to one I answered yesterday:

  http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=337775#bm337792

• abhijit jadhav

  Valued Member

  Points: 53

  More actions

  January 19, 2007 at 6:43 am

  #684271

  I can't change the existing procedure i.e, p1 becuse of dependencies...

  Where in procedure p1 the o/p is generated by simple select statment using @variables

  like

  select @clclientname,@clclintid

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  January 19, 2007 at 7:04 am

  #684282

  Then I think you need to use a temp table, something like this (tweak this to suit your own requirements):

  CREATE TABLE #t1 (clName char(10), clID int)

  INSERT INTO #t1 EXEC p1

  DECLARE @myclname char(10)

  SELECT @myclname = MAX(clName)

  FROM #t1

  EXEC p2 @param1 = @myclname

  John

• abhijit jadhav

  Valued Member

  Points: 53

  More actions

  January 19, 2007 at 7:42 am

  #684302

  Thats Excellent!!!!!

  Thank you very much...