首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

话语/过程的调用

2012-08-15 
语句/过程的调用在同一个.sql文件中,多个位置出现对同一语句/过程的使用,如何实现语句/过程的复用?SQL cod

语句/过程的调用
在同一个.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文件,文件名后不加当日号数@d        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中,bak文件名后不加当日号数@d)        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,文件名后加上当日号数@d)        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


如代码所示,多个位置用到了执行完整备份的语句,唯一不同的地方仅仅是@BackupFile变量拼接中增/减一个@d而已。如何做到像下面这样:
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文件,文件名后不加当日号数@d        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中,bak文件名后不加当日号数@d)        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,文件名后加上当日号数@d)        SET @BackupFile = ''        SET @BackupFile = @BackupDir + @fh1 + @DBName + @fh2 + @BackClass + @fh2 + @ym + @fh2 + @d + @bak        >>>>>>>>调用完整备份语句块    END--------------------------------------------------------------------------    DROP TABLE #t 



[解决办法]
1、把相同的部分写个存储过程 添加参数
2、把相同的部分写到最下面 加个标志 使用goto 语句

热点排行