Create procedure in else block on SQL Server

Question

I have a SQL script in which I generate a database and its tables, stored procedures, views, etc. I have used following script to generate the database and all the things. I was able to generate tables but on stored procedure it's giving an error:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.'

Here is the code. I have used an if/else block so can't use a go statement. I check if the database exists or not and take appropriate action. Any one has solution for it?

After EDIT:

 IF EXISTS(SELECT name FROM sys.databases WHERE name = 'SampleDB')
 begin
 PRINT 'Exist.'
 end
 Else
 begin
 CREATE DATABASE SampleDB
 exec sp_dboption N'SampleDB', N'autoshrink', N'false'
 exec sp_dboption N'SampleDB', N'ANSI null default', N'false'
 exec sp_dboption N'SampleDB', N'recursive triggers', N'false'
 exec sp_dboption N'SampleDB', N'ANSI nulls', N'false'
 exec sp_dboption N'SampleDB', N'concat null yields null', N'false'
 exec sp_dboption N'SampleDB', N'cursor close on commit', N'false'
 exec sp_dboption N'SampleDB', N'default to local cursor', N'false'
 exec sp_dboption N'SampleDB', N'quoted identifier', N'false'
 exec sp_dboption N'SampleDB', N'ANSI warnings', N'false'
 exec sp_dboption N'SampleDB', N'auto create statistics', N'true'
 exec sp_dboption N'SampleDB', N'auto update statistics', N'true'
 DECLARE @sql nvarchar(MAX)
 SET @sql='
 use SampleDB
 CREATE TABLE [dbo].[BrandMaster] (
 [BrandId] [int] IDENTITY (1, 1) CONSTRAINT [PK_BrandMaster] PRIMARY KEY NOT NULL ,
 [BrandName] [nvarchar] (50) NOT NULL ,
 [BrandStatus] [bit] NOT NULL
 )
 CREATE TABLE [dbo].[BrandProductMaster] (
 [BrandProductId] [int] IDENTITY (1, 1) CONSTRAINT [PK_BrandProductMaster] PRIMARY KEY NOT NULL ,
 [ProductId] [int] NOT NULL ,
 [BrandId] [int] NOT NULL ,
 [Units] [nvarchar] (15) NULL ,
 [Status] [bit] NOT NULL
 )
 CREATE TABLE [dbo].[BrokerMaster] (
 [BrokerId] [int] IDENTITY (1, 1) CONSTRAINT [PK_BrokerMaster] PRIMARY KEY NOT NULL ,
 [BrokerName] [nvarchar] (100) NOT NULL ,
 [BrokerPercentage] [float] NOT NULL ,
 [BrokerAddress] [nvarchar] (100) NULL ,
 [BrokerTelephoneNo] [bigint] NULL ,
 [BrokerMobileNo] [bigint] NULL ,
 [BrokerFaxNo] [bigint] NULL ,
 [BrokerEmailId] [nvarchar] (75) NULL ,
 [BrokerStatus] [bit] NOT NULL
 )
 INSERT INTO [dbo].[BrokerMaster] ([BrokerName],[BrokerPercentage],[BrokerAddress],[BrokerEmailId],[BrokerStatus]) VALUES (''No Broker'',0.0,'''','''',1)
 ...........
.............
............
............../After creating list of tables,here goes for stored procedure and views
 EXECUTE (''CREATE PROCEDURE [dbo].[usp_ExtractAllBrokers] AS Select BrokerId, BrokerName, BrokerStatus,BrokerPercentage From BrokerMaster'')
 EXECUTE (''CREATE VIEW dbo.View_UserMaster AS SELECT UserId, UserCompanyId, UserFullName, Username,[Password],CASE UserStatus WHEN 1 THEN ''Active'' WHEN 0 THEN ''InActive'' END AS UserStatus, UserMobileNo, UserEmailId, CASE BrokerMaster WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS BrokerMaster,CASE CompanyMaster WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS CompanyMaster,CASE CustomerMaster WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS CustomerMaster,CASE TaxMaster WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS TaxMaster, CASE UserMaster WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS UserMaster,CASE VendorMaster WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS VendorMaster,CASE ProductMaster WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS ProductMaster,CASE Purchase WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS Purchase, CASE Sales WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS Sales,CASE Reporting WHEN 1 THEN ''Y'' WHEN 0 THEN ''N'' END AS Reporting, UserCreatedOn FROM dbo.UserMaster'')
 exec sp_addextendedproperty N''MS_DiagramPane1'', N''[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
 Begin DesignProperties = 
 Begin PaneConfigurations = 
 Begin PaneConfiguration = 0
 NumPanes = 4
 Configuration = "(H (1[40] 4[20] 2[20] 3) )"
 End
 Begin PaneConfiguration = 1
 NumPanes = 3
 Configuration = "(H (1 [50] 4 [25] 3))"
 End
 Begin PaneConfiguration = 2
 NumPanes = 3
 Configuration = "(H (1 [50] 2 [25] 3))"
 End
 Begin PaneConfiguration = 3
 NumPanes = 3
 Configuration = "(H (4 [30] 2 [40] 3))"
 End
 Begin PaneConfiguration = 4
 NumPanes = 2
 Configuration = "(H (1 [56] 3))"
 End
 Begin PaneConfiguration = 5
 NumPanes = 2
 Configuration = "(H (2 [66] 3))"
 End
 Begin PaneConfiguration = 6
 NumPanes = 2
 Configuration = "(H (4 [50] 3))"
 End
 Begin PaneConfiguration = 7
 NumPanes = 1
 Configuration = "(V (3))"
 End
 Begin PaneConfiguration = 8
 NumPanes = 3
 Configuration = "(H (1[56] 4[18] 2) )"
 End
 Begin PaneConfiguration = 9
 NumPanes = 2
 Configuration = "(H (1 [75] 4))"
 End
 Begin PaneConfiguration = 10
 NumPanes = 2
 Configuration = "(H (1[66] 2) )"
 End
 Begin PaneConfiguration = 11
 NumPanes = 2
 Configuration = "(H (4 [60] 2))"
 End
 Begin PaneConfiguration = 12
 NumPanes = 1
 Configuration = "(H (1) )"
 End
 Begin PaneConfiguration = 13
 NumPanes = 1
 Configuration = "(V (4))"
 End
 Begin PaneConfiguration = 14
 NumPanes = 1
 Configuration = "(V (2))"
 End
 ActivePaneConfig = 0
 End
 Begin DiagramPane = 
 Begin Origin = 
 Top = 0
 Left = 0
 End
 Begin Tables = 
 Begin Table = "UserMaster"
 Begin Extent = 
 Top = 6
 Left = 38
 Bottom = 121
 Right = 198
 End
 DisplayFlags = 280
 TopColumn = 1
 End
 End
 End
 Begin SQLPane = 
 End
 Begin DataPane = 
 Begin ParameterDefaults = ""
 End
 Begin ColumnWidths = 20
 Width = 284
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 Width = 1500
 End
 End
 Begin CriteriaPane = 
 Begin ColumnWidths = 11
 Column = 3210
 Alias = 2595
 Table = 1170
 Output = 720
 Append = 1400
 NewValue = 1170
 SortType = 1350
 SortOrder = 1530
 GroupBy = 1350
 Filter = 1350
 Or = 2025
 Or = 1350
 Or = 1350
 End
 End
 End
 '', N''user'', N''dbo'', N''view'', N''View_UserMaster''
 exec sp_addextendedproperty N''MS_DiagramPaneCount'', 1, N''user'', N''dbo'', N''view'', N''View_UserMaster''
 '
 EXECUTE sp_executesql @sql
 END

Giving me error at line of creating VIEW

Incorrect syntax near 'Active'.

Answer 1

I personally prefer not to do it this way myself as our company forces us to version everything we do - therefore we should know whether objects exist or not and produce a script which explicitly creates or alters objects.

That being said, if you need to do it this way then for each object type that must be executed within a batch by itself you will need to execute a separate command to alter/create them. For example:

IF OBJECT_ID(N'dbo.my_proc',N'P') IS NULL
EXECUTE ('CREATE PROCEDURE [dbo].[my_proc] AS SELECT 1');
EXECUTE ('GRANT EXEC ON [dbo].[my_proc] TO [some_user]');
ELSE
EXECUTE ('ALTER PROCEDURE [dbo].[my_proc] AS SELECT 2');
GO

EDIT

Based on your additional information, Max's solution will solve your problem. However, as he mentioned this is an extremely bad way of doing it as you spend more time ensuring you have quoted correctly rather than solving SQL errors.

A better way to do it, and what I was pointing to originally, would be to separate each component out into individual statements like so:

IF DB_ID(N'some_db') IS NOT NULL
BEGIN
PRINT 'EXISTS';
END
ELSE
BEGIN
IF OBJECT_ID(N'dbo.my_table',N'U') IS NULL
BEGIN
EXECUTE(
'CREATE TABLE [dbo].[my_table](
 [id] INT IDENTITY NOT NULL, 
 [name] NVARCHAR(8) NOT NULL,
 CONSTRAINT [pk_my_table] PRIMARY KEY([id]),
 CONSTRAINT [uk_my_table_name] UNIQUE([name])
 )');
END
IF OBJECT_ID(N'dbo.my_view',N'V')IS NULL
BEGIN
EXECUTE(
'CREATE VIEW [dbo].[my_view]
 AS
 SELECT [id],[name]
 FROM [dbo].[my_table]
 WHERE [name] LIKE ''A%'';'
);
END
IF OBJECT_ID(N'dbo.my_proc',N'P') IS NULL
BEGIN
EXECUTE(
'CREATE PROCEDURE [dbo].[my_proc](@id INT)
 AS
 SELECT [name]
 FROM [dbo].[my_view]
 WHERE [id] = @id;'
);
END
END
GO

Answer 2

Mr.Brownstone has the correct answer, I would prefer if you marked his question as correct.

Having said that you may need further help; so here goes. The sample below uses an nvarchar(max) variable to execute a command that contains an embedded nvarchar(max) used to create the stored procedure if it does not exist. This is an example of what not to do since it is almost impossible to debug this class of code.

USE Master;
GO
IF EXISTS (select name from sys.databases WHERE name = 'Test')
 PRINT 'Test database already exists, doing nothing';
ELSE
BEGIN
 DECLARE @cmd as NVARCHAR(max);
 SET @cmd = '
 CREATE DATABASE Test; /* CREATE A TEST DATABASE */
 GO
 USE Test; /* make the Test database active */
 GO
 /* if proc MakeIt exists, then drop the procedure (USE WITH CAUTION!) */
 IF EXISTS (SELECT name FROM sys.procedures WHERE name = ''MakeIt'')
 BEGIN
 DROP PROCEDURE MakeIt;
 END
 GO
 /* create a procedure to make the necessary tables and procedures 
 for this database */
 CREATE PROCEDURE MakeIt
 AS
 BEGIN
 DECLARE @cmd NVARCHAR(max);
 SET @cmd = '''';
 /* if TestTable does not exist, create it. */
 IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = ''TestTable'')
 BEGIN
 CREATE TABLE TestTable
 (
 TestTableID INT NOT NULL CONSTRAINT PK_TestTable 
 PRIMARY KEY CLUSTERED IDENTITY(1,1)
 , SomeData uniqueidentifier CONSTRAINT DF_TestTable_SomeData
 DEFAULT (NEWID())
 );
 END
 /* if SomeProc does not exist, create it */
 IF NOT EXISTS (SELECT ''SomeProc'' FROM sys.procedures WHERE name = ''SomeProc'')
 BEGIN
 SET @cmd = 
 ''
 CREATE PROCEDURE SomeProc
 @someParam uniqueidentifier
 AS
 BEGIN
 IF NOT EXISTS
 (
 SELECT TestTableID 
 FROM TestTable 
 WHERE SomeData = @someParam
 )
 BEGIN
 INSERT INTO TestTable (SomeData) VALUES (@someParam);
 END
 END
 '';
 /* use sp_executesql to create the procedure in its own batch, 
 negating the need for the ''GO'' statement */
 EXEC sp_executesql @cmd;
 END
 END
 ';
 EXEC sp_executesql @cmd;
END
USE Test;
GO
/* Run the procedure to create the table and stored proc */
EXEC MakeIt;
/* run the stored proc */
DECLARE @ID uniqueidentifier;
SET @ID = NEWID();
EXEC SomeProc @ID;
/* see the result */
SELECT * FROM TestTable;

If you have a DDL database trigger on the Test database you could track design changes, even changes made to the database by code similar to my example. See my answer regarding how to do that here: Who changed/modified stored procedure in SSMS 2005 or 2008?

• sharad: Max Vernon,Thanks for response was helpful.

Answer 3

You need to escape single quotes once more, since you are already "in" the string:

CASE UserStatus WHEN 1 THEN ''''Active'''' WHEN 0 THEN ''''InActive'''' END AS UserStatus

I would personally throw away all those sp_addextendedproperty procedures. Maybe you should consider writing your own change scripts, as it was pointed out in other answers. Also, GO is not a statement in SQL Server.