Sql-Server

在 SQL Server 的 else 塊中創建過程

  • July 5, 2017

我有一個 SQL 腳本，在其中生成數據庫及其表、儲存過程、視圖等。我使用以下腳本來生成數據庫和所有內容。我能夠生成表，但在儲存過程中它給出了一個錯誤：

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

這是程式碼。我使用了一個if/else塊，所以不能使用go語句。我檢查數據庫是否存在並採取適當的措施。有人有解決方案嗎？

編輯後：

   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

在創建 VIEW 的行給我錯誤

“活動”附近的語法不正確。

我個人不喜歡自己這樣做，因為我們公司強迫我們對我們所做的一切進行版本控制——因此我們應該知道對像是否存在並生成一個明確創建或更改對象的腳本。

話雖如此，如果您需要這樣做，那麼對於必須在批處理中單獨執行的每種對像類型，您將需要執行單獨的命令來更改/創建它們。例如：

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

編輯

根據您的附加資訊，Max 的解決方案將解決您的問題。然而，正如他所提到的，這是一種非常糟糕的做法，因為您會花費更多時間來確保正確引用而不是解決 SQL 錯誤。

一種更好的方法，以及我最初所指的，是將每個組件分成單獨的語句，如下所示：

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

引用自：https://dba.stackexchange.com/questions/35551

相關問答

Sql-Server

插入和更新的單獨儲存過程？

  • May 15, 2020
檢視問答
Sql-Server

使用 NOCOUNT 提高過程性能

  • May 13, 2020
檢視問答
Sql-Server

NVARCHAR(MAX) 字元串似乎有 6326 個字元，但不會全部列印

  • January 28, 2020
檢視問答
Sql-Server

我們如何獲得過程的輸出欄位？

  • June 25, 2019
檢視問答
Sql-Server

在更快的 CASE 語句中：“WHENF我___F一世和一世dfield在 (0)’ 或 ‘WHENF我___F一世和一世dfield= 0”？

  • May 5, 2019
檢視問答
Sql-Server

可以定期對所有視圖執行 sp_refreshview 嗎？

  • July 25, 2018
檢視問答