Professional Web Applications Themes

Execute Immediate in a stored procedure? - Microsoft SQL / MS SQL Server

Hi, I have a stored procedure that takes an argument of type varchar. I want to pass in a SQL statement and have the procedure execute it with the "EXECUTE IMMEDIATE" statement. In fact, the exact line of code is "EXECUTE IMMEDIATE SQLStatement" The problem is that I get an error that stored procedure IMMEDIATE cannot be found. Can somebody tell me what I'm doing wrong? And yes, I don know that this is not efficient code. The reason I am doing it this was is because I want to delete rows from a table, but I don't want to ...

  1. #1

    Default Execute Immediate in a stored procedure?

    Hi,

    I have a stored procedure that takes an argument of type
    varchar. I want to pass in a SQL statement and have the
    procedure execute it with the "EXECUTE IMMEDIATE"
    statement. In fact, the exact line of code is "EXECUTE
    IMMEDIATE SQLStatement"

    The problem is that I get an error that stored procedure
    IMMEDIATE cannot be found.

    Can somebody tell me what I'm doing wrong?

    And yes, I don know that this is not efficient code. The
    reason I am doing it this was is because I want to delete
    rows from a table, but I don't want to explicitly give
    delete permission to the user. This way, I have a bit
    more control over what is happening.

    Thanks,
    James
    James Guest

  2. #2

    Default Re: Execute Immediate in a stored procedure?

    If you simply put the DELETE statement inside the proc and grant EXEC of the proc to the user, then they don't have to have DELETE permission on the underlying table.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "James Bender" <com> wrote in message news:09be01c34fc3$b9ab3c90$gbl...
    Hi,

    I have a stored procedure that takes an argument of type
    varchar. I want to pass in a SQL statement and have the
    procedure execute it with the "EXECUTE IMMEDIATE"
    statement. In fact, the exact line of code is "EXECUTE
    IMMEDIATE SQLStatement"

    The problem is that I get an error that stored procedure
    IMMEDIATE cannot be found.

    Can somebody tell me what I'm doing wrong?

    And yes, I don know that this is not efficient code. The
    reason I am doing it this was is because I want to delete
    rows from a table, but I don't want to explicitly give
    delete permission to the user. This way, I have a bit
    more control over what is happening.

    Thanks,
    James

    Tom Guest

  3. #3

    Default Re: Execute Immediate in a stored procedure?

    your syntax is wrong it should be

    exec (SQLStatement)

    but it won't work. dynamic sql is executed in the security context of the
    caller not the stored procs, so it will not have delete permission. you must
    use standard sql in your proc if you want it to be able to perform the
    delete when not granted to the user.

    -- bruce (sqlwork.com)



    "James Bender" <com> wrote in message
    news:09be01c34fc3$b9ab3c90$gbl... 


    bruce Guest

Similar Threads

  1. execute stored procedure
    By Buda123 in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 29th, 09:26 PM
  2. Execute permission denied on 'stored procedure' dbo
    By Prasath in forum ASP.NET Web Services
    Replies: 0
    Last Post: October 13th, 08:57 AM
  3. Problem with execute to make a stored procedure
    By Cecil Westerhof in forum ASP Database
    Replies: 6
    Last Post: March 1st, 05:29 PM
  4. Replies: 2
    Last Post: October 21st, 08:01 AM
  5. How can I execute a stored procedure in .net?
    By FireListen in forum ASP.NET General
    Replies: 1
    Last Post: June 27th, 01:39 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