asked
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
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
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.
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.
