locked
Dynamic SQL in Function RRS feed

  • Question

  • can anybody suggest me. how to implement dynamic SQL in function.

     I have read all posts regarding this dynmaic sql implementation. they suggest CLR.

    i am searching for some other option?

    kindly reply.

    Thursday, July 14, 2011 4:46 AM

Answers

  • Dynamic SQL is not allowed in a function. You can use it in a stored procedure.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Mr. Wharty Thursday, July 14, 2011 7:44 AM
    • Marked as answer by Alex Feng (SQL) Friday, July 22, 2011 10:11 AM
    Thursday, July 14, 2011 4:48 AM

All replies

  • Dynamic SQL is not allowed in a function. You can use it in a stored procedure.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Mr. Wharty Thursday, July 14, 2011 7:44 AM
    • Marked as answer by Alex Feng (SQL) Friday, July 22, 2011 10:11 AM
    Thursday, July 14, 2011 4:48 AM
  • THANKS for your prompt reply. but calling procedure is a performance degredation. since i need to call in the columns for the table. thats why.
    Thursday, July 14, 2011 5:16 AM
  • Can you please explain your whole problem? Say, I recently found a huge performance degradation by using a function rather than an SP.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 14, 2011 5:20 AM
  • THANKS for your prompt reply. but calling procedure is a performance degredation. since i need to call in the columns for the table. thats why.

    A function is unlikely to perform better than a stored procedure in your given scenario so like Naomi, I'd be interested in you providing an example.
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Thursday, July 14, 2011 7:46 AM
  • If you post your problem we could try to find a solution without dynamic SQL.

    In every other case Naomi and Jeff are right!

    Thursday, July 14, 2011 8:38 AM
  • Hi, Just post your issue with scenario so that you can get the solution
    velmurugan.s
    Thursday, July 14, 2011 11:16 AM
  • Thanks all. i have decided to proceed by computing the functionality in a temp table and fetch from it.
    Thursday, July 14, 2011 11:34 AM