July 7, 2011 at 4:15 am
Hello,
Sorry for my english.... 😎
I want execute a dynamics Command sql in "Function Table".
DECLARE @myFirstRequest VARCHAR(8000);
select @myFirstRequest=Request_Text from T_REQUEST
where id_Request = @IdRequest
insert into @tt
SELECT E_D.ID from E_D where E_D.IDRETURN in (@myFirstRequest)
How may i do to keep the value of @myFirstRequest like a SQL command and not like a parameters ?
Thank you for your answer...
July 7, 2011 at 5:54 am
The function EXEC is impossible on a SQL function table....
July 7, 2011 at 6:01 am
My function Table :
ALTER FUNCTION [dbo].[Synchro_GetIds]
(
@IdRequest int;
)
RETURNS @tt TABLE
(
Ids int NOT NULL
)
AS
Begin
DECLARE @myFirstRequest VARCHAR(8000);
-- for obtain the filter request exemple : 'SELECT IDRETURN FROM DB_TEST where IDRETURN>1000'
select @myFirstRequest=Request_Text from T_REQUEST
where id_Request = @IdRequest
insert into @tt
SELECT E_D.ID from E_D where E_D.IDRETURN in (@myFirstRequest)
return;
END
July 7, 2011 at 6:10 am
i know logically, it makes sense to get the id's you are looking for in one table, adn then do a seperate step;
but from a good TSQL point of view, you could do it all in one step;
you really need to do it this way, since dynamic SQL is not permitted in a function.a functions not allowed to do any "side affecting" operations. since you could potentially write dynamic SQL that could change DDL, it's easier to simply no permit dynamic SQL insiode functions.
ALTER FUNCTION [dbo].[Synchro_GetIds]
(
@IdRequest int
)
RETURNS @tt TABLE
(
Ids int NOT NULL
)
AS
Begin
insert into @tt
SELECT
E_D.ID
from E_D
where E_D.IDRETURN in (select Request_Text
from T_REQUEST
where id_Request = @IdRequest)
return;
END
i'd consider taking this one step further and changing this to an inline table value function.
Lowell
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply