MSSQL 一个备份数据库的存储过程和一个插入数据的存储示例
SqlServer
2015-07-15
798
0
一个备份数据库的存储过程
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
0条评论