Functoin call in Dynamic SQL

Question

Functoin call in Dynamic SQL

ouertani neked

SSC-Addicted

Points: 406
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

olavho Say Hey Kid Points: 706 More actions · Answer 1

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 SSC-Addicted Points: 406 More actions · Answer 2

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 SSC-Addicted Points: 406 More actions · Answer 3

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: 65142 More actions · Answer 4

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 SSC-Addicted Points: 406 More actions · Answer 5

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