一个备份数据库的存储过程

IF OBJECT_ID('dbo.BackupDatabases', 'P') IS NOT NULL 
	DROP PROCEDURE dbo.BackupDatabases;
GO
CREATE PROCEDURE dbo.BackupDatabases
  @databasetype AS NVARCHAR(30)
AS
BEGIN
  DECLARE @databasename AS NVARCHAR(128)
  , @timecomponent AS NVARCHAR(50)
  , @sqlcommand AS NVARCHAR(1000);
  IF @databasetype NOT IN ('User', 'System')
    BEGIN
      THROW 50000, 'dbo.BackupDatabases: @databasename must be User or System', 0;
      RETURN;
  END;
  IF @databasetype = 'System'
    SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name IN 
       ('master', 'model', 'msdb'));
  ELSE
    SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name NOT IN
       ('master', 'model', 'msdb', 'tempdb'));
  WHILE @databasename IS NOT NULL
    BEGIN
      SET @timecomponent = REPLACE(REPLACE(REPLACE(CONVERT(
          NVARCHAR, GETDATE(), 120), ' ', '_'), ':', ''), '-', '');
      SET @sqlcommand = 'BACKUP DATABASE ' + @databasename + ' TO DISK = 
          ''C:\Backups\' + @databasename + '_' + @timecomponent + '.bak''';
      PRINT @sqlcommand;
      --EXEC(@sqlcommand);
      IF @databasetype = 'System'
        SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name IN
            ('master', 'model', 'msdb') AND name > @databasename);
      ELSE
        SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name NOT IN
            ('master', 'model', 'msdb', 'tempdb') AND name > @databasename);
  END;
  RETURN;
END
GO

插入数据的存储示例

IF OBJECT_ID('Production.InsertProducts', 'P') IS NOT NULL
    DROP PROCEDURE Production.InsertProducts;
GO
CREATE PROCEDURE Production.InsertProducts
    @productname AS NVARCHAR(40) ,
    @supplierid AS INT ,
    @categoryid AS INT ,
    @unitprice AS MONEY = 0 ,
    @discontinued AS BIT = 0
AS
    BEGIN
        DECLARE @ClientMessage NVARCHAR(100);
        BEGIN TRY
-- Test parameters
            IF NOT EXISTS ( SELECT  1
                            FROM    Production.Suppliers
                            WHERE   supplierid = @supplierid )
                BEGIN
                    SET @ClientMessage = 'Supplier id '
                        + CAST(@supplierid AS VARCHAR) + ' is invalid';
                    THROW 50000, @ClientMessage, 0;
                END;
            IF NOT EXISTS ( SELECT  1
                            FROM    Production.Categories
                            WHERE   categoryid = @categoryid )
                BEGIN
                    SET @ClientMessage = 'Category id '
                        + CAST(@categoryid AS VARCHAR) + ' is invalid';
                    THROW 50000, @ClientMessage, 0;
                END;
            IF NOT ( @unitprice >= 0 )
                BEGIN
                    SET @ClientMessage = 'Unitprice '
                        + CAST(@unitprice AS VARCHAR)
                        + ' is invalid. Must be >= 0.';
                    THROW 50000, @ClientMessage, 0;
                END;
-- Perform the insert
            INSERT  Production.Products
                    ( productname ,
                      supplierid ,
                      categoryid ,
                      unitprice ,
                      discontinued
                    )
            VALUES  ( @productname ,
                      @supplierid ,
                      @categoryid ,
                      @unitprice ,
                      @discontinued
                    );
        END TRY
        BEGIN CATCH
            THROW;
        END CATCH;
    END;
GO

EXEC Production.InsertProducts
@productname = 'Test Product'
, @supplierid = 10
, @categoryid = 1
, @unitprice = -100
, @discontinued = 0