Professional Web Applications Themes

UserDefined Fuctions and SQL - Microsoft SQL / MS SQL Server

Does anyone know how to insert or update inside a user- defined function, or have any advice on ways around this? Trent...

  1. #1

    Default UserDefined Fuctions and SQL

    Does anyone know how to insert or update inside a user-
    defined function, or have any advice on ways around this?

    Trent
    Trent Guest

  2. #2

    Default Re: UserDefined Fuctions and SQL

    Trent,
     

    You cannot do this in a user-defined function. You must use a stored
    procedure to modify data.

    From Books Online:

    "The following statements are allowed in the body of a
    multi-statement function. Statements not in this list are not
    allowed in the body of a function:

    * Assignment statements.

    * Control-of-Flow statements.

    * DECLARE statements defining data variables and cursors that are
    local to the function.

    * SELECT statements containing select lists with expressions that
    assign values to variables that are local to the function.

    * Cursor operations referencing local cursors that are declared,
    opened, closed, and deallocated in the function. Only FETCH
    statements that assign values to local variables using the INTO
    clause are allowed; FETCH statements that return data to the client
    are not allowed.

    * INSERT, UPDATE, and DELETE statements modifying table variables
    local to the function.

    * EXECUTE statements calling an extended stored procedures."


    Linda


    lindawie Guest

  3. #3

    Default Re: UserDefined Fuctions and SQL

    No, you cannot directly update any persistent tables using an UDF. However,
    you can build a 'wrapper' around the table with an UDF & do your updates
    like:

    CREATE TABLE tbl(
    key INT NOT NULL PRIMARY KEY,
    col INT) ;
    INSERT tbl VALUES(2,3) ;
    INSERT tbl VALUES(1,5) ;
    GO
    CREATE FUNCTION ufn()
    RETURNS TABLE
    AS
    RETURN(SELECT key, col
    FROM tbl) ;
    GO
    SELECT * FROM tbl ; -- before update
    GO
    UPDATE ufn()
    SET col = 4
    WHERE key = 1 ;
    GO
    SELECT * FROM tbl ; -- after update.
    GO

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

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