Invalid use of a side-effecting operator 'INSERT EXEC' within a function:

  • I do not see any relevance here but to satisfy your curiosity;-); let say ad-hoc!!

    Cheers,
    John Esraelo

  • John Esraelo (5/12/2009)


    I do not see any relevance here but to satisfy your curiosity;-); let say ad-hoc!!

    Okay, first, you aren't getting hammered on this site. I am trying to get as much information about the the problem as I can so that I can make reasonable suggestions. You may feel I am being difficult, sorry, but everything I am asking you I'd be asking of my users if I was in your position. The more information I have the more effective I can be.

    Please take a little time and read this blog entry: The Flip Side.

  • Nice posting by you, very nice...

    You must also consider the fact that there are "volunteers" out there that are trying to assist others and yet can not understand the clear posted problem by a DBA or a user. No one is perfect and so some "volunteers" and of course for those then it is highly recommended to sit back, RSS the the topic and learn something actually.

    Lynn, nice positing I like that and I will refer folks to that.

    I hope I have answered all your questions.

    thx

    Cheers,
    John Esraelo

  • John Esraelo (5/12/2009)


    Nice posting by you, very nice...

    You must also consider the fact that there are "volunteers" out there that are trying to assist others and yet can not understand the clear posted problem by a DBA or a user. No one is perfect and so some "volunteers" and of course for those then it is highly recommended to sit back, RSS the the topic and learn something actually.

    Lynn, nice positing I like that and I will refer folks to that.

    I hope I have answered all your questions.

    thx

    Nope. I still don't know how the users are going to use the dynamic view (which is one way of looking at a TVF). Still haven't figured out how they are using it.

  • John Esraelo (5/12/2009)


    Nice posting by you, very nice...

    You must also consider the fact that there are "volunteers" out there that are trying to assist others and yet can not understand the clear posted problem by a DBA or a user. No one is perfect and so some "volunteers" and of course for those then it is highly recommended to sit back, RSS the the topic and learn something actually.

    Lynn, nice positing I like that and I will refer folks to that.

    I hope I have answered all your questions.

    thx

    Also, if we volunteers don't understand the "clear problem" that was posted, does that mean it was actually clear to them? That is why some of us ask more questions, for clarity. It is the responsibility of the OP (Original Poster) to be sure that those who are trying to assist fully understand the problem, not the other way around.

    If they seem confused, like I am still here, then it means I haven't been told enough to make it clear. Remember, I don't know your data or system where as you do.

  • This Sample uses OPENQuery and works!

    1) Create a new Linked Server: (Configure: Data Acess, RPC In, RPT OUT).

    2) Create 2 functions below:

    go

    create FUNCTION fnteste(@name varchar(50))

    RETURNS TABLE

    AS

    RETURN

    (

    select * from openquery(SQL,'sp_dinamic') where name = @name or @name is null

    )

    go

    create proc sp_dinamic

    @name varchar(50) = null

    as

    set nocount on

    declare @sql nvarchar(4000)

    declare @rowcount int

    if(@name is not null)

    select @sql = 'select name from sysobjects where xtype=''u'' and name='''+@name+''' order by name asc'

    else

    select @sql = 'select name from sysobjects where xtype=''u'' order by name asc'

    exec @rowcount = sp_executesql @sql

    go

    /*Teste*/

    select * from dbo.fnteste(null)

    And so....

    Have a nice day...

  • This one sure generated a lot of discussion but I don't think (at least I did not see) that anyone addressed the actual code problem in the UDF the OP presented.

    The code includes an Exec statement but MS Documentation pretty clearly states no dynamic SQL in UDF. See BOL

    User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

  • Ray Herring (8/22/2016)


    This one sure generated a lot of discussion but I don't think (at least I did not see) that anyone addressed the actual code problem in the UDF the OP presented.

    The code includes an Exec statement but MS Documentation pretty clearly states no dynamic SQL in UDF. See BOL

    User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

    One, 6 year old thread and the OP quit responding.

    Two, actually I think the OP understood that user-defined functions cannot make use of dynamic SQL, just didn't seem to understand why.

Viewing 8 posts - 46 through 53 (of 53 total)

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