在同一个.sql文件中，多个位置出现对同一语句/过程的使用，如何实现语句/过程的复用？
- SQL code
-- 检查完整备份目录和bak文件是否存在 SET @BackClass = 'Full' SET @BackName = @DBName + @fh2 + @BackClass SET @BackupDir = '' SET @BackupDir = @Drv + @fh1 + @BackClass --目录 USE master CREATE TABLE #t(a INT,b INT,c INT) INSERT INTO #t EXEC xp_fileexist @BackupDir --bak文件 SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @bak EXEC xp_fileexist @BackupFile , @num OUTPUT -- (1 = 存在，0 = 不存在)-------------------------------------------------------------------------- -- 无目录 IF exists(SELECT * FROM #t WHERE b=0) BEGIN --创建目录 DECLARE @CMDStr VARCHAR(100) SET @cmdstr = 'mkdir' + ' ' + @BackupDir EXEC xp_cmdshell @CMDStr --执行完整备份，备份到新的bak文件，[email protected] SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @bak BACKUP DATABASE @DBName TO DISK = @BackupFile WITH NOFORMAT, NOINIT, NAME = @BackName, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM END-------------------------------------------------------------------------- -- 有目录无文件 IF exists(SELECT * FROM #t WHERE b=1) AND @num = 0 BEGIN -- 执行完整备份（备份到新的bak中，[email protected]） SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @bak BACKUP DATABASE @DBName TO DISK = @BackupFile WITH NOFORMAT, NOINIT, NAME = @BackName, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM END-------------------------------------------------------------------------- -- 有目录有文件 IF exists(SELECT * FROM #t WHERE b=1) AND @num = 1 BEGIN -- 执行完整备份，备份到新的bak，[email protected]） SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @fh2 + @d + @bak BACKUP DATABASE @DBName TO DISK = @BackupFile WITH NOFORMAT, NOINIT, NAME = @BackupName, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM END-------------------------------------------------------------------------- DROP TABLE #t
如代码所示，多个位置用到了执行完整备份的语句，[email protected][email protected]：
- SQL code
-----------完整备份语句块------------------ BACKUP DATABASE @DBName TO DISK = @BackupFile WITH NOFORMAT, NOINIT, NAME = @BackName, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM[/color] -- 检查完整备份目录和bak文件是否存在 SET @BackClass = 'Full' SET @BackName = @DBName + @fh2 + @BackClass SET @BackupDir = '' SET @BackupDir = @Drv + @fh1 + @BackClass --目录 USE master CREATE TABLE #t(a INT,b INT,c INT) INSERT INTO #t EXEC xp_fileexist @BackupDir --bak文件 SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @bak EXEC xp_fileexist @BackupFile , @num OUTPUT -- (1 = 存在，0 = 不存在)-------------------------------------------------------------------------- -- 无目录 IF exists(SELECT * FROM #t WHERE b=0) BEGIN --创建目录 DECLARE @CMDStr VARCHAR(100) SET @cmdstr = 'mkdir' + ' ' + @BackupDir EXEC xp_cmdshell @CMDStr --执行完整备份，备份到新的bak文件，[email protected] SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @bak >>>>>>>>调用完整备份语句块 END-------------------------------------------------------------------------- -- 有目录无文件 IF exists(SELECT * FROM #t WHERE b=1) AND @num = 0 BEGIN -- 执行完整备份（备份到新的bak中，[email protected]） SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @bak >>>>>>>>调用完整备份语句块 END-------------------------------------------------------------------------- -- 有目录有文件 IF exists(SELECT * FROM #t WHERE b=1) AND @num = 1 BEGIN -- 执行完整备份，备份到新的bak，[email protected]） SET @BackupFile = '' SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @fh2 + @d + @bak >>>>>>>>调用完整备份语句块 END-------------------------------------------------------------------------- DROP TABLE #t