Any equivalent of "Create or Replace"?

Hi,
Is there any equivalent in SQL server for oracle statement "Create or
Replace" such as CREATE OR REPLACE PROCEDURE......
As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
DROP and a CREATE can't reside in the same query batch file. I want to put
the whole script (for creating a new procedure or ALTERing an existing one)
in one file and be able to run it.
Any thoughts?
Thanks,
RamRam,
No, not to my knowledge. The next best way for any objects other than tables
is:
If the object exists
drop it
GO
Create the object
I do this also with tables but I have to combine them with ALTER scripts.
You don't want to create a table more than once, normally.
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing
one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>|||Thanks Ron,
I found a way to do this taking cue from your advice:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[MyStoredProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [MyStoredProc]
GO
CREATE PROCEDURE MyStoredProc.....
GO
In this way, I can run this query batch file and change it at any time if I
want to. This was part of a requirement to do version control on stored
procedures and now I am happy with the solution. And you are right, we won't
be doing this for tables.
Thanks,
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Ram,
> No, not to my knowledge. The next best way for any objects other than
tables
> is:
> If the object exists
> drop it
> GO
> Create the object
> I do this also with tables but I have to combine them with ALTER scripts.
> You don't want to create a table more than once, normally.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is there any equivalent in SQL server for oracle statement "Create or
> > Replace" such as CREATE OR REPLACE PROCEDURE......
> >
> > As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> > DROP and a CREATE can't reside in the same query batch file. I want to
put
> > the whole script (for creating a new procedure or ALTERing an existing
> one)
> > in one file and be able to run it.
> >
> > Any thoughts?
> >
> > Thanks,
> > Ram
> >
> >
>|||ALTER PROCEDURE would overwrite the existing procedure with the same name.
Actually, as the name suggestion, it would ALTER the existing Sproc.
"Ram P. Dash" wrote:
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
>|||Ram,
This way uses a little bit less code and doesn't access a system table:
IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
DROP PROCEDURE dbo.MyStoredProc
GO
If you have good naming conventions there's no need to use the undocumented
'P' parameter:
IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
DROP PROCEDURE dbo.usp_MyStoredProc
GO
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> Thanks Ron,
> I found a way to do this taking cue from your advice:
> IF EXISTS (SELECT * FROM sysobjects WHERE id =OBJECT_ID(N'[MyStoredProc]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [MyStoredProc]
> GO
> CREATE PROCEDURE MyStoredProc.....
> GO
> In this way, I can run this query batch file and change it at any time if
I
> want to. This was part of a requirement to do version control on stored
> procedures and now I am happy with the solution. And you are right, we
won't
> be doing this for tables.
> Thanks,
> Ram
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > Ram,
> >
> > No, not to my knowledge. The next best way for any objects other than
> tables
> > is:
> >
> > If the object exists
> > drop it
> > GO
> > Create the object
> >
> > I do this also with tables but I have to combine them with ALTER
scripts.
> > You don't want to create a table more than once, normally.
> >
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> > "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> > news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > Is there any equivalent in SQL server for oracle statement "Create or
> > > Replace" such as CREATE OR REPLACE PROCEDURE......
> > >
> > > As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also
a
> > > DROP and a CREATE can't reside in the same query batch file. I want to
> put
> > > the whole script (for creating a new procedure or ALTERing an existing
> > one)
> > > in one file and be able to run it.
> > >
> > > Any thoughts?
> > >
> > > Thanks,
> > > Ram
> > >
> > >
> >
> >
>|||That's exactly what I want.
Thanks a lot Ron. You Rock!
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:ebH6Q6IuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Ram,
> This way uses a little bit less code and doesn't access a system table:
> IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
> DROP PROCEDURE dbo.MyStoredProc
> GO
> If you have good naming conventions there's no need to use the
> undocumented
> 'P' parameter:
> IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
> DROP PROCEDURE dbo.usp_MyStoredProc
> GO
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
>> Thanks Ron,
>> I found a way to do this taking cue from your advice:
>> IF EXISTS (SELECT * FROM sysobjects WHERE id => OBJECT_ID(N'[MyStoredProc]')
>> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> DROP PROCEDURE [MyStoredProc]
>> GO
>> CREATE PROCEDURE MyStoredProc.....
>> GO
>> In this way, I can run this query batch file and change it at any time if
> I
>> want to. This was part of a requirement to do version control on stored
>> procedures and now I am happy with the solution. And you are right, we
> won't
>> be doing this for tables.
>> Thanks,
>> Ram
>> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
>> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
>> > Ram,
>> >
>> > No, not to my knowledge. The next best way for any objects other than
>> tables
>> > is:
>> >
>> > If the object exists
>> > drop it
>> > GO
>> > Create the object
>> >
>> > I do this also with tables but I have to combine them with ALTER
> scripts.
>> > You don't want to create a table more than once, normally.
>> >
>> > Ron
>> > --
>> > Ron Talmage
>> > SQL Server MVP
>> >
>> > "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
>> > news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
>> > > Hi,
>> > >
>> > > Is there any equivalent in SQL server for oracle statement "Create or
>> > > Replace" such as CREATE OR REPLACE PROCEDURE......
>> > >
>> > > As far as I know, SQL Server doesn't support "CREATE OR REPLACE".
>> > > Also
> a
>> > > DROP and a CREATE can't reside in the same query batch file. I want
>> > > to
>> put
>> > > the whole script (for creating a new procedure or ALTERing an
>> > > existing
>> > one)
>> > > in one file and be able to run it.
>> > >
>> > > Any thoughts?
>> > >
>> > > Thanks,
>> > > Ram
>> > >
>> > >
>> >
>> >
>>
>