sp_executesql and several outputs in store procs

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  November 28, 2005 at 2:30 pm

  #102961

  Hi guys........

  I have to call a store proc (a and b are inputs, c,d and e are outputs):

  SET @nstrSQL = 'EXEC @RetError = usp_myStoreProc ' + char(39) + @a + char(39) + ', ' + char(39)+ cast(@b as varchar(10)) + char(39) + ', @C OUTPUT, @d OUTPUT,  @e OUTPUT'

  I would like to use sp_executesql to retrieve outputs and return errors.

  EXEC sp_executesql @nstrSQL, N' @RetError int output', @RetError OUTPUT .....

  How can I do?

  I tried but i receive an error sayng that c is not declared, but al the variable are declared.

  Thank for any useful suggestions...

  

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 28, 2005 at 5:02 pm

  #606258

  @C is not declared inside of executed SQL string.

  You declare only N' @RetError int output', why you love it more than all other variables in your script?

  _____________
  Code for TallyGenerator

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  November 29, 2005 at 2:00 pm

  #606541

  I have declared also other parameters but the result is the same...

   

  any help will be very appreciated...

   

   

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  November 29, 2005 at 2:05 pm

  #606543

  I have declared also other parameters but the result is the same...

   

  any help will be very appreciated...

   

   

• noeld

  SSC Guru

  Points: 96590

  More actions

  November 29, 2005 at 2:47 pm

  #606555

  why would you use sp_execsql if calling the procedure directly will give you what you need:

  exec @RetError = usp_myStoreProc @a, @b-2, @C out, @d out, @e out

   

  
  * Noel

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  November 30, 2005 at 2:10 pm

  #606871

  I use sp_execsql because some parameters are integer and with comma (,) between paramters they are misunderstood from t-sql.

• noeld

  SSC Guru

  Points: 96590

  More actions

  November 30, 2005 at 2:14 pm

  #606874

  if what you mean by (,) between parameter.. is that you need to convert them then just do it befor the call:

  ex:

  declare @i int, @a int , @b-2 varchar(20), @C int, @d int, @e varchar(10)

  set @b-2 = cast( @i as varchar(20))

  exec @RetError = usp_myStoreProc @a, @b-2, @C out, @d out, @e out

   

  still no need for sp_executesql

   

   

  
  * Noel