Functoin call in Dynamic SQL

• ouertani neked

  Old Hand

  Points: 356

  More actions

  June 24, 2004 at 3:31 am

  #152199

  

  Hi every body, I'm trying to call (or even execute) this function and I get a message telling: Only "extended" function and procs can be called from funtion ( I hope that my translation from french is Ok)

  Thanks:

  ALTER    FUNCTION GetActSp_Id(@DbName varchar(50), @spec  varchar(30))

  RETURNS _ID

  AS

  BEGIN

  Declare @SQL nvarchar(500)

  DECLARE @ActSpId _ID

  set @ActSpId = null

  SET @SQL = 'DECLARE ActSpCursor CURSOR READ_ONLY'

  + ' FOR SELECT ActSp_Id FROM ' + @DbNameDestination + '.dbo.ACTSP'

  + ' WHERE ActSp_Libelle = ' + [dbo].[quotestring](@Spec)

  EXEC sp_executesql @sql

  OPEN ActSpCursor     

  FETCH NEXT FROM ActSpCursor INTO @ActSpId

  CLOSE ActSpCursor

  DEALLOCATE ActSpCursor

  RETURN(@ActSpId)

  END

• olavho

  SSChasing Mays

  Points: 646

  More actions

  June 24, 2004 at 7:18 am

  #511775

  Hi,

  I don't think you are allowed to call "normal" procedures from a function. The only exception (for some reason) are extended procedures.

• ouertani neked

  Old Hand

  Points: 356

  More actions

  June 24, 2004 at 7:56 am

  #511785

  

  any solution? this is critical to my program because i'm calling this function from an other stored proc that is also using dynamic sql; type:

  INSERT INTO (a, b, c, ...) (SELECT a , function(...), c, ...)

  so for the other case I can change my program shape to get the values first then affect them in the INSERTstatement, but for the Upper case I can't do so! otherwise I'll be obliged to use a cursor and slow down the performance. Please help!

• ouertani neked

  Old Hand

  Points: 356

  More actions

  June 25, 2004 at 2:34 am

  #511916

  

  Hi every body this is the real example: any help is more than welcome:

  this is the calling procedure:

  ALTER      PROCEDURE PS_Migration_DBMED_PABI_2 @DbnameSource  _label, @DbNameDestination _label

  AS

  SET NOCOUNT ON

  Declare @SQL nvarchar(1200)

  BEGIN TRAN

  Set @SQL = 'INSERT INTO ' + @DbNameDestination + '..PABI'

  + '( PA_ID)'

  + ' SELECT ' + @DbNameDestination + '.dbo.GetPaId(' + [dbo].[quotestring](@DbNameDestination)+ ', NoDos)'

  + ' FROM ' + @DbnameSource + '..PARAM'

  EXEC sp_executesql @sql

  if @@ERROR <> 0 ROLLBACK TRAN

  Else COMMIT TRAN

  SET NOCOUNT OFF

  This is the called function:

  ALTER    FUNCTION GetPaId(@DbNameDestination _label, @numdoss int)

  RETURNS _id

  AS

  BEGIN

  Declare @SQL nvarchar(500)

  DECLARE @idpa _id

  SET @SQL = 'DECLARE PaCursor CURSOR READ_ONLY'

  + ' FOR SELECT pa_id FROM ' + @DbNameDestination + '.dbo.PA'

  + ' WHERE pa_numdossier = ' +  [dbo].[quotestring](@numdoss)

  EXEC sp_executesql @sql

  OPEN paCursor

  FETCH NEXT FROM paCursor INTO @idpa

  CLOSE paCursor

  DEALLOCATE paCursor

  RETURN(@idpa)

  END

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  June 25, 2004 at 7:31 am

  #511948

  How about this

  ALTER PROCEDURE PS_Migration_DBMED_PABI_2 @DbnameSource  _label, @DbNameDestination _label

  AS

  SET NOCOUNT ON

  Declare @SQL nvarchar(1200)

  BEGIN TRAN

  SET @SQL = 'INSERT INTO ' + @DbNameDestination + '..PABI

  (PA_ID)

  SELECT

  (

  SELECT MIN(pa_id)

  FROM  ' + @DbNameDestination.dbo.PA + '

  WHERE pa_numdossier = NoDos

  )

  FROM  ' + @DbnameSource..PARAM

  EXEC sp_executesql @SQL

  if @@ERROR <> 0 ROLLBACK TRAN

  Else COMMIT TRAN

  SET NOCOUNT OFF

  Far away is close at hand in the images of elsewhere.
  Anon.

• ouertani neked

  Old Hand

  Points: 356

  More actions

  June 25, 2004 at 8:08 am

  #511964

  Once again thank you David, even when you're a little bit late you still of great help, I was just trying imbricate sql and ineeded a confirmation that it is a good choice

  cheers