Sql-server – Create procedure in else block on SQL Server

if-not-existssql serversql-server-2008stored-procedures

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'.

Best Answer

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

Best Answer

Related Solutions

Sql-server – Why is this stored procedure for linked server job failing in SQL Server Agent

Sql-server – Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

Related Question