Thursday, 28 August 2008

SQL Server Management Studio Madness - why do I have to have scripts as strings?

What do you think the following option in the SQL Server Management Studio does?

SSMS Option

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:

Mark Hurd said...

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.