Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>> Hello,
>> Very quick question (I hope) regarding SQL Server:
>> I want to create a query that basically says that if a stored procedure
>> (say
>> sproc_foo) does not exist, then create the stored procedure. I know that
>> there are easy ways to do this with creating tables/databases if they
>> don't
>> exist, but how is this possible with stored procedures?
>> Regards,
>> James Simpson
>> Straightway Technologies Inc.|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >
> > Hi James,
> >
> > You can try something like this
> >
> > IF NOT EXISTS (SELECT * FROM sys.objects
> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> > AND type in (N'P', N'PC'))
> > BEGIN
> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> > ...
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "James Simpson" wrote:
> >
> >> Hello,
> >> Very quick question (I hope) regarding SQL Server:
> >> I want to create a query that basically says that if a stored procedure
> >> (say
> >> sproc_foo) does not exist, then create the stored procedure. I know that
> >> there are easy ways to do this with creating tables/databases if they
> >> don't
> >> exist, but how is this possible with stored procedures?
> >>
> >> Regards,
> >>
> >> James Simpson
> >> Straightway Technologies Inc.
> >>
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>> Ben,
>> Alas, that will not work as is, since CREATE PROCEDURE must be the first
>> statement in a batch. Scripting from SQL Server 2005 with the If Not
>> Exists
>> check turned on produces:
>> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =>> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
>> BEGIN
>> EXEC dbo.sp_executesql @.statement = N'
>> CREATE PROC [dbo].[MyProc]
>> @.Parm1 char(6)
>> AS
>> -- Do Something'
>> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
>> there is a CONNECT request for something better than this.)
>> RLF
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>> >
>> > Hi James,
>> >
>> > You can try something like this
>> >
>> > IF NOT EXISTS (SELECT * FROM sys.objects
>> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
>> > AND type in (N'P', N'PC'))
>> > BEGIN
>> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
>> > ...
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "James Simpson" wrote:
>> >
>> >> Hello,
>> >> Very quick question (I hope) regarding SQL Server:
>> >> I want to create a query that basically says that if a stored
>> >> procedure
>> >> (say
>> >> sproc_foo) does not exist, then create the stored procedure. I know
>> >> that
>> >> there are easy ways to do this with creating tables/databases if they
>> >> don't
>> >> exist, but how is this possible with stored procedures?
>> >>
>> >> Regards,
>> >>
>> >> James Simpson
>> >> Straightway Technologies Inc.
>> >>
>>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> >
> > Hi Russell,
> >
> > I agree with your note but that was not intended to be a final and
> > complete
> > solution and my primary purpose was to show the IF NOT EXISTS part.
> >
> > Perhaps it is like saying that your code does not work as is either,
> > because
> > you have BEGIN but END is missing :-)
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ben,
> >>
> >> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> >> statement in a batch. Scripting from SQL Server 2005 with the If Not
> >> Exists
> >> check turned on produces:
> >>
> >> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => >> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> >> BEGIN
> >> EXEC dbo.sp_executesql @.statement = N'
> >> CREATE PROC [dbo].[MyProc]
> >> @.Parm1 char(6)
> >> AS
> >> -- Do Something'
> >>
> >> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> >> there is a CONNECT request for something better than this.)
> >>
> >> RLF
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >> >
> >> > Hi James,
> >> >
> >> > You can try something like this
> >> >
> >> > IF NOT EXISTS (SELECT * FROM sys.objects
> >> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> >> > AND type in (N'P', N'PC'))
> >> > BEGIN
> >> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> >> > ...
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> > Senior Database Administrator
> >> > AIG SunAmerica
> >> >
> >> >
> >> >
> >> > "James Simpson" wrote:
> >> >
> >> >> Hello,
> >> >> Very quick question (I hope) regarding SQL Server:
> >> >> I want to create a query that basically says that if a stored
> >> >> procedure
> >> >> (say
> >> >> sproc_foo) does not exist, then create the stored procedure. I know
> >> >> that
> >> >> there are easy ways to do this with creating tables/databases if they
> >> >> don't
> >> >> exist, but how is this possible with stored procedures?
> >> >>
> >> >> Regards,
> >> >>
> >> >> James Simpson
> >> >> Straightway Technologies Inc.
> >> >>
> >>
> >>
> >>
>
>