x

Problem writing one script to Drop, re-Create, and Grant Execute Permissions to a stored procedure

Hi,

I'm hoping that someone may know something about this: We're writing a script (t-sql in SQL Server 2008) to all in one: 1) Drop a stored procedure, then 2)create the just dropped stored procedure, and finally 3) grant Execute permissions to the newly written procedure.

The problem is, the code we wrote when executed doesn't work. However we've pulled (from SQL Compare) code that does the exact same thing, which works (for the creation and graning permissions). We've compared the code of both scripts, the SQL Compare code works, but ours (written by hand) doesn't and they are practically identical.

Does anyone have any ideas how to help us get our hand-written code to work?

Thanks
more ▼

asked Dec 10 '10 at 11:28 AM in Default

Head_Contusion gravatar image

Head_Contusion
33 2 2 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The most likely reason is the lack of the go terminator between the drop and create. If this is the case then you are probably greeted with lovely

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

error. Please check that your are not missing any batch terminators. One of the most evil things to do in T-SQL is to forget terminating the procedure definition, because without it, the procedure text will include the rest of the script you are trying to execute, which can lead to some really unwanted results. Once I accidentally ran into the existing procedure definition which included something as nasty as checking existence and creation of some index, simply because the procedure definition did not end with the word GO, several scripts were compiled into a single script and the whole batch then got deployed. You can use the template like this for your create proc script:

-- drop proc your_proc_name
if  exists 
(
    select * from sys.objects 
        where 
            [object_id] = object_id(N'[dbo].[your_proc_name]') 
            and [type] in (N'P', N'PC')
)
    drop procedure [dbo].[your_proc_name];
go

set ansi_nulls on;
go
set quoted_identifier on;
go

-- create proc your_proc_name
create proc dbo.your_proc_name
(
    @your_param varchar(50)
)
as

begin;

    set nocount on;

    select
        your_column_list
        from your_tables
        where your_column = @your_param

    set nocount off;

end;
go

-- grant execute permissions to your_role
grant execute on dbo.your_proc_name to your_role_name;
go
Oleg
more ▼

answered Dec 10 '10 at 11:39 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

Oleg,

Thank you for the response. I am learning a lot about SQL Server as I go, so I hope that you don't mind if I have a couple questions about your post?

Where the errors for the hand-written script seem to be appearing is on the lines with the GO statement.

I thought the set "set ansi_nulls on;" and the "set quoted_identifier on;" statements were automatically just included (by SQL Server) when you hand-write a stored procedure (when not right-clicking on the Stored Procedures folder in the GUI and selecting 'New Stored Procedure'.

Will adding the "set nocount off;", "set ansi_nulls on;", "begin;", etc. make a diffeence in how the script performs. The SQL Compare tool that wrote a comparable script (which resembled ours) didn't have any of those lines of code in it, but seemed to work pretty well (although it didn't do the drop, it performed the creation of and permissions granting flawlessly).

Thank you very much for your insight and help.
Dec 10 '10 at 12:21 PM Head_Contusion
The settings set ansi_nulls on; and set quoted_identifier on; should be added if they are not present. These are called sticky settings, they are very important, and cannot be changed inside of the body of the stored procedure. In other words, if you forget to add, say, set ansi_nulls on; but your procedure logic really needs it then in case if the session settings have it off for some obscure reason, changing it inside of the proc definition will not work. Since you never want to rely on the session settings of the future code which will call your proc, you will potenially save yourself a lot of trouble by simply adding the sticky settings before the proc definition. set nocount on; is usually added after begin and set nocount off; (optionally) before end. The setting is responsible for supressing the X rows affected messages which show up in the messages pane. The setting is therefore desired. GO is not technically a T-SQL command, but is rather a batch separator. It must be present by itself on the separate line and cannot have ; after it. Do not forget to add GO after end. Please reply if you have any more questions. Thanks.
Dec 13 '10 at 02:20 PM Oleg
Great information, thank you very much, Oleg.
Dec 22 '10 at 11:40 AM Head_Contusion
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x977
x401
x83

asked: Dec 10 '10 at 11:28 AM

Seen: 2725 times

Last Updated: Dec 13 '10 at 02:22 PM