首页 > 代码库 > T-SQL 片段收藏

T-SQL 片段收藏

2024-07-03 11:42:51   229人阅读

 

存储过程

 1 CREATE PROCEDURE spInsertOrUpdateProduct
 2 --有则更新，否则插入
 3     @ProductName NVARCHAR(50) ,
 4     @ProductNumber NVARCHAR(25) ,
 5     @StdCost MONEY
 6 AS
 7     IF EXISTS ( SELECT  *
 8                 FROM    Production.Product
 9                 WHERE   ProductNumber = @ProductNumber )
10         UPDATE  Production.Product
11         SET     Name = @ProductName ,
12                 StandardCost = @StdCost
13         WHERE   ProductNumber = @ProductNumber
14     ELSE
15         INSERT  INTO Production.Product
16                 ( Name ,
17                   ProductNumber ,
18                   StandardCost
19                 )
20                 SELECT  @ProductName ,
21                         @ProductNumber ,
22                         @StdCost
23                     
24 GO
View Code

 触发器

 1 CREATE TRIGGER tr_DelProduct ON Production.Product
 2     FOR DELETE
 3 AS
 4     IF ( SELECT COUNT(*)
 5          FROM   sales.SalesOrderDetail
 6                 INNER JOIN DELETED ON salesorderdetail.ProductID = DELETED.productid
 7        ) > 0
 8         BEGIN
 9             RAISERROR (‘Cannot delete a product with sales orders‘,14,1)
10             ROLLBACK TRANSACTION
11             RETURN
12         END
View Code

 自定义函数

 1 CREATE FUNCTION dbo.fn_LastOfMonth ( @TheDate DATETIME )
 2 RETURNS DATETIME
 3 AS
 4     BEGIN
 5         DECLARE @FirstOfMonth DATETIME
 6         DECLARE @DaysInMonth INT
 7         DECLARE @RetDate DATETIME
 8         SET @FirstOfMonth = DATEADD(mm, DATEDIFF(mm, 0, @TheDate), 0)
 9         SET @DaysInMonth = DATEDIFF(d, @FirstOfMonth,
10                                     DATEADD(m, 1, @FirstOfMonth))
11         RETURN DATEADD(d,@DaysInMonth-1,@FirstOfMonth)
12     END
View Code

 查看表的元数据，也就是数据的数据

1 SELECT  *
2 FROM    sys.columns
3 WHERE   [object_id] = OBJECT_ID(‘Production.Product‘)

不建议用上面的系统试图
可以用数据库视图

1 IF NOT EXISTS ( SELECT  *
2                 FROM    INFORMATION_SCHEMA.TABLES
3                 WHERE   TABLE_NAME = ‘Address‘
4                         AND TABLE_NAME = ‘Person‘ )
5     DROP TABLE PERSON.Address
6 GO
View Code

 修改对象

 1 ALTER PROCEDURE spInsertOrUpdateProduct
 2     @ProductName NVARCHAR(50) ,
 3     @ProductNumber NVARCHAR(25) ,
 4     @StdCost MONEY ,
 5     @ListPrice MONEY
 6 AS
 7     BEGIN TRY
 8         BEGIN TRANSACTION
 9         IF EXISTS ( SELECT  *
10                     FROM    Production.Product
11                     WHERE   ProductNumber = @ProductName )
12             UPDATE  Production.Product
13             SET     Name = @ProductName ,
14                     StandardCost = @StdCost
15             WHERE   ProductNumber = @ProductNumber
16         ELSE
17             INSERT  INTO production.Product
18                     ( Name ,
19                       ProductNumber ,
20                       StandardCost ,
21                       ListPrice
22                     )
23                     SELECT  @ProductName ,
24                             @ProductNumber ,
25                             @StdCost ,
26                             @ListPrice
27         COMMIT TRANSACTION
28     END TRY
29     BEGIN CATCH
30         DECLARE @ErrMsg VARCHAR(1000)
31         SET @ErrMsg = ERROR_MESSAGE()
32         ROLLBACK TRANSACTION
33         RAISERROR(@ErrMsg,14,1)
34         RETURN
35     END CATCH
View Code

 添加和删除表列

1 ALTER TABLE Production.Product
2 ADD LeadTime SMALLINT NULL
3 
4 ALTER TABLE production.Product
5 DROP COLUMN LeadTime
View Code

 WITH TIES用法，找出最贵的一个商品，但最贵的有好多个

1 SELECT TOP(1) WITH TIES * FROM Production.Product
2 ORDER BY ListPrice DESC 
3 --返回5条记录
View Code

 

 


联系
我们