C#写SQL SERVER2008存储过程

在C#中，选择"新建项目"->"数据库"->"SQL Server"->"Visual C# SQL CLR 数据库"

在项目中，单击右键选择"添加"->"存储过程",代码如下(SalesCrossTabs.cs)：

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 using System.Data.SqlTypes;
 5 using Microsoft.SqlServer.Server;
 6 
 7 
 8 public partial class StoredProcedures
 9 {
10     [Microsoft.SqlServer.Server.SqlProcedure]
11     public static void GetSalesPerTerritoryByMonth(SqlDateTime StartDate,SqlDateTime EndDate)
12     {
13         // 在此处放置代码
14         SqlCommand command = new SqlCommand();
15         command.Connection = new SqlConnection("Context connection=true");
16         command.Connection.Open();
17 
18         string sql = "select DISTINCT Convert(char(7),h.OrderDate,120) As YYYY_MM " +
19             "FROM Sales.SalesOrderHeader h " +
20             "WHERE h.OrderDate BETWEEN @StartDate AND @EndDate "+
21             "ORDER BY YYYY_MM";
22         command.CommandText = sql.ToString();
23 
24         SqlParameter param = command.Parameters.Add("@StartDate", SqlDbType.DateTime);
25         param.Value = StartDate;
26         param = command.Parameters.Add("@EndDate", SqlDbType.DateTime);
27         param.Value = EndDate;
28 
29         SqlDataReader reader = command.ExecuteReader();
30 
31         System.Text.StringBuilder yearsMonths=new System.Text.StringBuilder();
32         while (reader.Read())
33         {
34             yearsMonths.Append("["+(string)reader["YYYY_MM"]+"],");
35         }
36         //close the reder
37         //MessageBox.Show(yearsMonths.ToString());
38         reader.Close();
39 
40         if (yearsMonths.Length > 0)
41         {
42             yearsMonths.Remove(yearsMonths.Length - 1, 1);
43         }
44         else
45         {
46             command.CommandText =
47                 "RAISEERROR('No date present for the input date range.',16,1)";
48             try
49             {
50                 SqlContext.Pipe.ExecuteAndSend(command);
51             }
52             catch
53             {
54                 return;
55             }
56         }
57 
58         //Define the cross-tab query
59         sql =
60             "Select TerritoryId," +
61                 yearsMonths.ToString() +
62             "From " +
63             "(" +
64                 "SELECT  " +
65                     "TerritoryId, " +
66                     "CONVERT(char(7),h.OrderDate,120) AS YYYY_MM, " +
67                     "d.LineTotal " +
68                 "From Sales.SalesOrderHeader h " +
69                 "JOIN Sales.SalesOrderDetail d " +
70                     " ON h.SalesOrderID=d.SalesOrderID " +
71                 " WHERE h.OrderDate between @StartDate AND @EndDate " +
72             ") p " +
73             "PIVOT " +
74             "( " +
75                 "SUM (LineTotal) " +
76                 "FOR YYYY_MM IN " +
77                 "( " +
78                     yearsMonths.ToString() +
79                 ") " +
80             ") As pvt " +
81             "ORDER BY TerritoryId";
82 
83         //set the CommandText
84         command.CommandText = sql.ToString();
85 
86         //have the caller execute the cross-tab query
87         SqlContext.Pipe.ExecuteAndSend(command);
88 
89         //close the connection
90         command.Connection.Close();
91     }
92 };

生成并部署此存储过程，在Test.sql中写测试语句
USE AdventureWorks; 
Exec GetSalesPerTerritoryByMonth @StartDate='20070501',@EndDate='20070701';