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

Mr.Brownstone · Accepted Answer · 2013-02-28 10:47:33Z

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

Community · Accepted Answer · 2017-04-13 12:42:52Z

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?

Max Vernon,Thanks for response was helpful.

sharad
– sharad

2013-02-28 12:00:55 +00:00
Commented Feb 28, 2013 at 12:00 — sharad
– sharad, Commented Feb 28, 2013 at 12:00

bojan · Accepted Answer · 2013-02-28 09:17:38Z

2

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.

answered Feb 28, 2013 at 9:17

bojan

4386 silver badges10 bronze badges

Add a comment |

Stack Exchange Network

Create procedure in else block on SQL Server

3 Answers 3

Your Answer

Linked

Hot Network Questions

Create procedure in else block on SQL Server

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions