T-SQL 片段收藏

 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

 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

 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

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

 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

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

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

 1 CREATE TABLE [Test]
 2     (
 3       [id] [int] IDENTITY(1, 1)
 4                  NOT NULL ,
 5       [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS
 6                             NULL ,
 7       [subject] [nvarchar](50) COLLATE Chinese_PRC_CI_AS
 8                                NULL ,
 9       [Source] [numeric](18, 0) NULL
10     )
11 ON  [PRIMARY]
12 GO
13 INSERT  INTO [test]
14         ( [name], [subject], [Source] )
15 VALUES  ( N'张三', N'语文', 60 ) ,
16         ( N'李四', N'数学', 70 ),
17         ( N'王五', N'英语', 80 ),
18         ( N'王五', N'数学', 75 ),
19         ( N'王五', N'语文', 57 ),
20         ( N'李四', N'语文', 80 ),
21         ( N'张三', N'英语', 100 );
22 Go 
23 
24 SELECT  *
25 FROM    test
26 
27 -------方法一----------
28 SELECT  name ,
29         SUM(CASE subject
30               WHEN '数学' THEN source
31               ELSE 0
32             END) AS '数学' ,
33         SUM(CASE subject
34               WHEN '英语' THEN source
35               ELSE 0
36             END) AS '英语' ,
37         SUM(CASE subject
38               WHEN '语文' THEN source
39               ELSE 0
40             END) AS '语文'
41 FROM    test
42 GROUP BY name
43 ------方法二--------
44 DECLARE @sql VARCHAR(8000)
45 SET @sql = 'select name,'
46 SELECT  @sql = @sql + 'sum(case subject when ''' + subject + ''' 
47 then source else 0 end) as ''' + subject + ''','
48 FROM    ( SELECT DISTINCT
49                     subject
50           FROM      test
51         ) AS a
52 SELECT  @sql = LEFT(@sql, LEN(@sql) - 1) + ' from test group by name'
53 EXEC(@sql)
54 go

 1 USE Northwind
 2 GO
 3 
 4 DECLARE curProduct CURSOR
 5 FOR
 6     SELECT  ProductID ,
 7             ProductName
 8     FROM    dbo.Products
 9 
10 DECLARE @ProdID INT
11 DECLARE @ProdName NVARCHAR(100)
12 
13 OPEN curProduct
14 FETCH NEXT FROM curProduct INTO @ProdID, @ProdName
15 WHILE @@FETCH_STATUS = 0
16     BEGIN
17         PRINT @ProdName            
18         FETCH NEXT FROM curProduct INTO @ProdID, @ProdName
19     END
20 
21 CLOSE curProduct
22 DEALLOCATE curProduct
23 ---------------------------------------------

 1 USE AdventureWorks2008
 2 GO
 3 SELECT  ProductID ,
 4         Name ,
 5         ListPrice ,
 6         ProductSubcategoryID ,
 7         CASE ProductSubcategoryID
 8           WHEN 1 THEN 'Mountain Bike'
 9           WHEN 2 THEN 'Road Bike'
10           WHEN 3 THEN 'Touring Bike'
11           WHEN NULL THEN 'Something Else'
12           ELSE '(No SubCategory)'
13         END AS SubCategory
14 FROM    Production.Product
15 ----------------------------------------