Professional Web Applications Themes

Dynamic SQL with variables in user-defined Functions - Microsoft SQL / MS SQL Server

I need to create a dynamic SQL statement in a user-defined function, but the statement needs to return values back into local variables: SELECT s = N'select num=TheColumn FROM MyTable' to do this, I need to use sp_executesql: EXEC sp_executesql s, N'num int OUTPUT', num OUTPUT but SQL Server does not allow a function to call a stored procedure. I also cannot use temporary tables because SQL Server also does not allow temporary tables in functions. Any way to accomplish this? Bruce...

  1. #1

    Default Dynamic SQL with variables in user-defined Functions

    I need to create a dynamic SQL statement in a user-defined function, but the
    statement needs to return values back into local variables:

    SELECT s = N'select num=TheColumn FROM MyTable'

    to do this, I need to use sp_executesql:

    EXEC sp_executesql s, N'num int OUTPUT', num OUTPUT

    but SQL Server does not allow a function to call a stored procedure. I also
    cannot use temporary tables because SQL Server also does not allow temporary
    tables in functions.

    Any way to accomplish this?

    Bruce


    Bruce Guest

  2. #2

    Default Re: Dynamic SQL with variables in user-defined Functions

    You don't need to use dynamic SQL to assign values to local variables.
    Is there some reason you can't simply include the following SELECT
    statement in your function?

    SELECT num = TheColumn FROM MyTable

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Bruce" <com> wrote in message
    news:phx.gbl... 
    but the 
    also 
    temporary 


    Dan Guest

  3. #3

    Default Re: Dynamic SQL with variables in user-defined Functions

    I neglected to mention that another part of the sql statement (a table name)
    is added inside a cursor loop. Like:

    SELECT s = N'select num=TheColumn FROM ' + CurrentTable

    Bruce

    ------------------------------
    "Dan Guzman" <net> wrote in message
    news:%phx.gbl... 


    Bruce Guest

  4. #4

    Default Re: Dynamic SQL with variables in user-defined Functions

    Variable object names are problematic because these require dynamic SQL
    and limit your options. Check out Erland's article on dynamic SQL
    considerations:

    http://www.algonet.se/~sommar/dynamic_sql.html

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP


    "Bruce" <com> wrote in message
    news:phx.gbl... 
    name) [/ref]
    variables. 
    >
    >[/ref]


    Dan Guest

Similar Threads

  1. User-defined functions...
    By AZDeveloper in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: June 15th, 07:49 PM
  2. Triggers and User Defined Trigger Functions
    By Gordan in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: March 9th, 03:09 PM
  3. User Defined Functions, and other stories....
    By Brian McGee in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 02:41 PM
  4. How and where to create User-Defined functions
    By Serge Rielau in forum IBM DB2
    Replies: 0
    Last Post: June 30th, 05:16 PM
  5. user defined functions and recursive selects
    By Serge Rielau in forum IBM DB2
    Replies: 0
    Last Post: June 30th, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139