how to exec sp_executesql inside a function?

  • Hi,

    I am trying to figure out how I can execute sp_executesql inside a function.

    The purpose of that function is to accept table name, column name and PK of table as input parameter and it will return the value.  I plan on using sql_variant as the data type so that I can browse the col of any table (and save on some joins)

    (I have already implemented this logic in a stored proc.  I am very curious to know if we can implement this (executing dynamic sql) inside of a function.

     

    example -

    declare @tbl varchar(30), @col varchar(30), @pkcol varchar(30), @pk varchar(6), @sql nvarchar(255)

    select @tbl = 'z_prop', @col = 'sysdate', @pkcol = 'property', @pk = 'AZ083'

    select @sql = 'Select ' +  @col + ' from ' + @tbl + ' where ' + @pkcol + ' = ''' + @pk + ''''

    --select @sql -> Select sysdate from z_prop where property = 'AZ083'

    exec sp_executesql @sql

     

    Thanks in advance.

  • sp_executesql CAN'T be used inside a UDF !

    what are you trying to accomplish?

    Save some joins aren't you doing it inside the function

    I believe you may want to create views instead

     


    * Noel

  • It appears that you can't.

    You can execute extended stored procedure inside a function and, even though sp_executesql is an extended stored procedure (despite its name), it still generates the message "only functions and extended stored procedures can be executed within a function".

    Which leads me to believe that sp_executesql may itself be calling one or two standard stored procedures.

     

     


    Cheers,
    - Mark

  • i am trying to optimize by saving on joins.  yes, I am creating several views, and i know I cannot execute sql_executesql inside an UDF.  I was wondering if there was a workaround.

  • There is one more thing to consider.

    The batch and the dynamic statement both run in different scopes. While the dynamic for example has access to tables created in the batch, the batch has no access to object and/or data created in the dynamic statement.

    This might also be a point.

    However, maybe you can post the whole thing you have so far along what you want as the result, so we might figure out a workaround or another solution.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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