March 11, 2010 at 10:58 am
Oracle PL/SQL has a syntax extension for creating procedures, functions, triggers, etc. Instead of having to write code to drop a code module, then create it, then reapply the now deleted grants/revokes (assuming one knows what they were), you can just issue a "create or replace" command. You don't have to care whether the code module already exists, and it doesn't trash the grants on the code module either. Do you think t-sql should get syntax like this added to the language?
March 11, 2010 at 11:03 am
Post it on Connect and see how people vote. Posted here the dev team isn't going to see it.
p.s. Why drop, create, fix permissions at all? That's what ALTER is there for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2010 at 11:10 am
You are right, I could use alter if the object exists.
The whole point of "create or replace" is that I don't have to know, OR CARE, if the object exists!
It cuts out the need for a whole bunch of bozo code to check if the item exists and delete it if it does.
March 11, 2010 at 11:22 am
david_wendelken (3/11/2010)
You are right, I could use alter if the object exists.The whole point of "create or replace" is that I don't have to know, OR CARE, if the object exists!
It cuts out the need for a whole bunch of bozo code to check if the item exists and delete it if it does.
And if the object doesn't exist, do you just create it without taking any sort of look at permissions?
I prefer something like this:
if OBJECT_ID(N'dbo.MyProcName','P') is null
and OBJECT_ID(N'dbo.MyProcName') is not null
begin
raiserror('Another object with the same name already exists.', 21, 1)
end;
else
if OBJECT_ID(N'dbo.MyProcName','P') is null
and OBJECT_ID(N'dbo.MyProcName') is null
begin
exec('create proc dbo.MyProcName as return;grant the right permissions to the right accounts/users/roles');
go
alter proc dbo.MyProcName
as
... put the script for the proc here ...;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2010 at 1:22 pm
The point is that I don't have to write all of that code to test for it's existence.
March 11, 2010 at 2:03 pm
Using templates or code snippets, it's basically write once use many times.
March 12, 2010 at 6:51 am
david_wendelken (3/11/2010)
The point is that I don't have to write all of that code to test for it's existence.
And my point is that this way controls permissions whether it already exists or not, but "create or replace" only handles permissions if it's replacing. Mine actually takes less work and less attention, assuming you create a good template for this kind of thing.
I don't write that much code every time. I already have most of it in files, I just fill in the blanks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2010 at 5:20 pm
I would have to weigh in on the side of permissions. If create or replace only handles them in the event of a replace, then what does it really save me?
The use of a template simplifies it dramatically.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2010 at 10:26 pm
Voted 'no'.
The separation of CREATE/ALTER does more good than harm, in my experience.
SQL Server != Oracle
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 5:36 am
Well, I've been using create or replace for over 10 years now.
I remember when I had to code in Oracle before it was introduced, and how much simpler the coding and administration of objects became after it was introduced.
I'm sorry some of you can't see it and that I'm failing to explain it well enough for you to do so.
March 13, 2010 at 6:13 am
david_wendelken (3/13/2010)
I'm sorry some of you can't see it and that I'm failing to explain it well enough for you to do so.
It is not that I cannot see the benefits; nor is it that you have not explained it well.
I have used Oracle a fair bit, and appreciate the convenience.
Having worked with SQL Server for an awful lot longer, I feel quite qualified to hold an opinion that differs from yours on this specific point.
My opinion is an aggregate of my experiences. For me, the disadvantages outweigh the minimal advantages.
That is all.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 9:06 am
david_wendelken (3/13/2010)
Well, I've been using create or replace for over 10 years now.I remember when I had to code in Oracle before it was introduced, and how much simpler the coding and administration of objects became after it was introduced.
I'm sorry some of you can't see it and that I'm failing to explain it well enough for you to do so.
I have to agree with Paul. I don't see any advantage to the CREATE OR REPLACE in SQL Server. You still have to deal with permissioning if the object is created, which means conditional logic after the create or replace to determine if permissions need to be set. What advantage is there in that?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply