SQL 脚本在使用 sys.obejcts 进行预检查时运行错误

Question

我想写一个脚本，它的功能是创建一个存储过程，在创建它之前，脚本应该必须检查它是否存在于DB中，所以我编写了如下过程，如果我注释掉前2行，如果存储过程不存在，脚本将成功创建过程，但我添加了前两行， SSMS 将引发错误：

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[User_Own_Restore_From_Job]') AND type in (N'P'))
CREATE PROCEDURE [dbo].[User_Own_Restore_From_Job]
@dbname varchar(500),
@backuppath varchar (500)
AS

BEGIN
SET NOCOUNT ON 
DECLARE @XPConfig int = 1
--To keep the existing value
SELECT @XPConfig = cast(value AS INT) FROM sys.configurations WHERE name LIKE 'xp_cmdshell';
--Enabling xp_cmdshell for getting registry and creating directory structure
BEGIN TRY
EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE with override
END TRY
BEGIN CATCH
--empty
END CATCH
DECLARE    @datapath nvarchar(500);
DECLARE    @logpath nvarchar(500);
--read data and log path details from registry
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerSetup', N'SQLDataRoot', @datapath OUTPUT
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @logpath OUTPUT
SET @datapath=@datapath+'Data';
--creating a database folder if not already exists on log and data drives
DECLARE @datapath2 varchar(500)=''
DECLARE @logpath2 varchar(500)=''
SET @DataPath2 = @datapath+ N''+@dbname
SET @LogPath2 = @logpath+ N''+@dbname
--reading all folders from DATA directory 
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath
--creating a folder on DATA drive with database name if not already exists
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath2

DELETE FROM @DirTree
--reading all folders from LOG directory 
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath
--creating a folder on LOG drive with database name if not already exists
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath2
DECLARE @PhysicalName nvarchar(260)=''
DECLARE @LogicalName nvarchar(128)=''
DECLARE @Type char(1)=''
DECLARE @text nvarchar(2000)=''
DECLARE @sql nvarchar(max)=''
DECLARE @restoredb TABLE ( LogicalName nvarchar(128), PhysicalName nvarchar(260),Type char(1)
,FileGroupName nvarchar(128),Size numeric(20,0),MaxSize numeric(20,0),FileID bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0)
,UniqueID uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int
,FileGroupID int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier
,IsReadOnly bit,IsPresent bit,TDEThumbprint varbinary(32),SnapshotUrl nvarchar(128)) 
--reading header section of backup file
INSERT @restoredb EXECUTE(N'RESTORE FILELISTONLY FROM DISK = '''+@backuppath +'''')
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
BEGIN TRY
--***taking backup of all permissions**********************************
CREATE TABLE #Permissions (id int identity(1,1), qry nvarchar(4000),Stat bit default 0)
SET @sql='USE ['+@dbname+'];
-- Create Users If not exists
INSERT INTO #Permissions (qry)
select 
txt=case when isnull(l.name,'''')=''''
then ''IF NOT EXISTS (SELECT 1 FROM sys.database_principals where name = '''''' + p.name + '''''') BEGIN  CREATE USER ['' + p.name +''] FOR LOGIN ['' + p.name +''] END''
else ''IF NOT EXISTS (SELECT 1 FROM sys.database_principals where name = '''''' + p.name + '''''') BEGIN  CREATE USER ['' + p.name +''] FOR LOGIN ['' + l.name +''] END''
end
from sys.database_principals p
left join master..syslogins l on l.sid = p.sid 
where p.type NOT IN (''R'') AND p.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
UNION ALL
--Create Role If not exists
select 
txt=case when isnull(l.name,'''')=''''
then ''IF NOT EXISTS (SELECT 1 FROM sys.database_principals where name = '''''' + p.name + '''''') BEGIN CREATE Role ['' + p.name +''] END''
else ''''
end
from sys.database_principals p
left join master..syslogins l on l.sid = p.sid
where p.type =''R'' and  p.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',
''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator'',''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'',''public'')
UNION ALL
--User Permissions
select 
txt=case when isnull(p.type ,'''')<>''G''
then ''ALTER USER ['' + p.name +''] WITH DEFAULT_SCHEMA = ''+ isnull(p.default_schema_name, ''[NULL]'') +'';''
else ''''
end
+ '' EXEC sp_addrolemember '''''' + q.name + '''''', '''''' + p.name + '''''';''
from sys.database_principals p
join (select * from sys.database_principals a 
join  sys.database_role_members r on a.principal_id=r.role_principal_id) q
on q.member_principal_id = p.principal_id
where (p.type NOT IN (''R'')) AND p.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
UNION ALL
--Orphaned users
select 
txt=case when isnull(l.name,'''')=''''
then ''Exec sp_change_users_login ''''update_one'''', '''''' + p.name + '''''', '''''' + p.name + '''''' ''  
else ''Exec sp_change_users_login ''''update_one'''', '''''' + p.name + '''''', '''''' + l.name  + '''''' ''
end
from sys.database_principals p
left join master..syslogins l on l.sid = p.sid 
where p.type=''S'' AND p.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
'
--get all permissions in text format
EXECUTE sp_executesql @sql
--**Permissions end******************************************************************--
--**preparing a restore statement****************************************************--
SET @SQL='USE [master];ALTER DATABASE ['+@dbname+N'] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ['+@dbname+N'] SET  SINGLE_USER;
ALTER DATABASE ['+@dbname+N'] set offline with NO_WAIT;'
--set database to single user mode
EXECUTE  sp_executesql @sql
SET @SQL='RESTORE DATABASE ['+@dbname+N'] FROM DISK = '''+@backuppath+''' WITH FILE = 1'
--preparing move statement for all files
DECLARE c1 CURSOR FOR SELECT PhysicalName,LogicalName,Type FROM  @restoredb 
OPEN c1;
FETCH NEXT FROM c1 INTO @PhysicalName,@LogicalName,@Type -- fetch first value
WHILE @@fetch_status = 0 
BEGIN
SET  @text = RIGHT(@PhysicalName,CHARINDEX( '', REVERSE(@PhysicalName)));
SET @SQL=@SQL+', MOVE '''+@LogicalName+N''''
IF (@Type = 'L')
SET @SQL=@SQL+'   TO '''+@logpath+N''+@dbname+@text+N''''
ELSE
SET @SQL=@SQL+'   TO '''+@datapath+N''+@dbname+@text+N''''
FETCH NEXT FROM c1 INTO @PhysicalName,@LogicalName,@Type  -- fetch next value
END; --WHILE @@fetch_status = 0 
CLOSE c1
DEALLOCATE c1; 
-- replace ersetzt die alte db
SET @SQL=@SQL+', NOUNLOAD , replace , STATS = 10 ;' 
--perform restore database operation
EXECUTE  sp_executesql @sql
--setting database to online mode    
SET @SQL='USE [master];
ALTER DATABASE ['+@dbname+N'] set ONLINE with NO_WAIT;
ALTER DATABASE ['+@dbname+N'] SET  MULTI_USER; '
EXECUTE  sp_executesql @sql
-- changes database owner to sa
SET @SQL='USE [' + @dbname + N'];
EXEC sp_changedbowner ''sa'' '
EXECUTE  sp_executesql @sql
--**Database a restore is completed**********************************************--
--**restore permissions on database****************************************************--
DECLARE @ptxt nvarchar(4000)='',@Id int=0
WHILE 0=0
BEGIN
SELECT @ptxt='',@Id=0
SELECT TOP 1 @Id=Id, @ptxt=qry FROM #Permissions WHERE Stat=0 ORDER BY Id        
IF Isnull(@Id,0)<=0 BREAK;
SET @ptxt= N'USE [' + @dbname + N'];' + @ptxt
--skip if any invalid login/user exists
BEGIN TRY    
--print @ptxt
EXECUTE (@ptxt)
END TRY
BEGIN CATCH
--No action is needed
END CATCH
UPDATE #Permissions SET Stat=1 WHERE Id=@Id
END
END TRY
BEGIN CATCH
--setting database to online mode    
SET @SQL='USE [master];
ALTER DATABASE ['+@dbname+N'] set ONLINE with NO_WAIT;
ALTER DATABASE ['+@dbname+N'] SET  MULTI_USER; '
EXECUTE  sp_executesql @sql
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
BEGIN TRY      
EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
--Retain original setting
EXEC sp_configure 'xp_cmdshell', @XPConfig
RECONFIGURE with override
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END

错误消息如下所示：

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Line 69
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 70
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 79
Must declare the scalar variable "@DBName".
Msg 137, Level 15, State 2, Line 90
Must declare the scalar variable "@DBName".
Msg 137, Level 15, State 2, Line 105
Must declare the scalar variable "@backuppath".
Msg 137, Level 15, State 2, Line 116
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 177
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 184
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 198
Must declare the scalar variable "@dbname".
......

如果我想进行预检查，谁能为我解释如何解决此问题？

Answer 1

CREATE PROCEDURE必须是批处理中唯一的语句。要有条件地执行它，您可以使用动态 SQL。

...
IF ...
BEGIN
EXECUTE(N'CREATE PROCEDURE ...');
END;
...

如果只需要条件执行来防止已存在的过程错误，并且 SQL Server 版本足够高，则可以使用CREATE OR ALTER PROCEDURE ...语句。我相信它自 2016 年以来就可用。这样，如果过程已经存在，则过程只会被覆盖，而不会因为过程已经存在而引发任何错误。

Answer 2

基本上约束是'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

根据你想要什么，有几个选项，每个都有不同的效果。

CREATE OR ALTER PROCEDURE

create or alter procedure [dbo].[User_Own_Restore_From_Job]
as
begin
...
end;
GO

效果是过程被更改，并且每次执行时都会更新sys.procedures中的modify_date

DROP PROCEDURE+CREATE PROCEDURE

如果您使用的是没有CREATE OR ALTER选项的早期版本的SQL Server，则可以检查存储过程是否存在，将其删除并创建。

if object_id('dbo.User_Own_Restore_From_Job') is not null
drop procedure dbo.User_Own_Restore_From_Job
GO
create procedure [dbo].[User_Own_Restore_From_Job]
as
begin
...
end;
GO

效果是每次执行过程时都会删除并创建过程。而且create_date和modify_date也将发生变化。

如果存在，则不执行

这利用了set noexec设置。如果该过程存在，请将noexec设置为off(后续命令将不执行(。并在最后将其设置回on。

IF  object_id('dbo.User_Own_Restore_From_Job') is not null
set noexec on;
GO
create procedure [dbo].[User_Own_Restore_From_Job]
as
begin
...
end;
GO
set noexec off;