What do you think the following option in the SQL Server Management Studio does?
Now one side of my brain says that I'll get a some preamble on my script looking a little bit like
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[SomeTable]'))Well you'd be right! Well in the main.
Without the option:
/****** Object: Table [dbo].[SomeTable] Script Date: 08/28/2008 09:03:29 ******/
CREATE TABLE [dbo].[SomeTable](
[Column1] [nchar](10) NULL,
[Column2] [nchar](10) NULL
) ON [PRIMARY]
And with the option:
/****** Object: Table [dbo].[SomeTable] Script Date: 08/28/2008 09:03:49 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SomeTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SomeTable](
[Column1] [nchar](10) NULL,
[Column2] [nchar](10) NULL
) ON [PRIMARY]
END
But for stored procedures?
Without the option:
/****** Object: StoredProcedure [dbo].[DoIt] Script Date: 08/28/2008 09:10:53 ******/
create procedure [dbo].[DoIt]
as
select *
from SomeTable
where Column1 = 'blah'
With the option:
/****** Object: StoredProcedure [dbo].[DoIt] Script Date: 08/28/2008 09:10:35 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoIt]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create procedure [dbo].[DoIt]
as
select *
from SomeTable
where Column1 = ''blah''
'
END
What I want to know is why does the 'IF NOT EXISITS' check require me to generate a script as a string invocation of sp_executesql?
1 comment:
SQL doesn't like CREATE PROCEDURE except as a command on its own.
In SQL Server 2005 it is called a Syntax Error.
As to why; I don't know if it is just one of the vagaries and non-orthogonality of SQL, or if there was real reason.
Post a Comment