C#写SQL SERVER2008存储过程

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

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

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetSalesPerTerritoryByMonth(SqlDateTime StartDate,SqlDateTime EndDate)
    {
        // 在此处放置代码
        SqlCommand command = new SqlCommand();
        command.Connection = new SqlConnection("Context connection=true");
        command.Connection.Open();

        string sql = "select DISTINCT Convert(char(7),h.OrderDate,120) As YYYY_MM " +
            "FROM Sales.SalesOrderHeader h " +
            "WHERE h.OrderDate BETWEEN @StartDate AND @EndDate "+
            "ORDER BY YYYY_MM";
        command.CommandText = sql.ToString();

        SqlParameter param = command.Parameters.Add("@StartDate", SqlDbType.DateTime);
        param.Value = StartDate;
        param = command.Parameters.Add("@EndDate", SqlDbType.DateTime);
        param.Value = EndDate;

        SqlDataReader reader = command.ExecuteReader();

        System.Text.StringBuilder yearsMonths=new System.Text.StringBuilder();
        while (reader.Read())
        {
            yearsMonths.Append("["+(string)reader["YYYY_MM"]+"],");
        }
        //close the reder
        //MessageBox.Show(yearsMonths.ToString());
        reader.Close();

        if (yearsMonths.Length > 0)
        {
            yearsMonths.Remove(yearsMonths.Length - 1, 1);
        }
        else
        {
            command.CommandText =
                "RAISEERROR('No date present for the input date range.',16,1)";
            try
            {
                SqlContext.Pipe.ExecuteAndSend(command);
            }
            catch
            {
                return;
            }
        }

        //Define the cross-tab query
        sql =
            "Select TerritoryId," +
                yearsMonths.ToString() +
            "From " +
            "(" +
                "SELECT  " +
                    "TerritoryId, " +
                    "CONVERT(char(7),h.OrderDate,120) AS YYYY_MM, " +
                    "d.LineTotal " +
                "From Sales.SalesOrderHeader h " +
                "JOIN Sales.SalesOrderDetail d " +
                    " ON h.SalesOrderID=d.SalesOrderID " +
                " WHERE h.OrderDate between @StartDate AND @EndDate " +
            ") p " +
            "PIVOT " +
            "( " +
                "SUM (LineTotal) " +
                "FOR YYYY_MM IN " +
                "( " +
                    yearsMonths.ToString() +
                ") " +
            ") As pvt " +
            "ORDER BY TerritoryId";

        //set the CommandText
        command.CommandText = sql.ToString();

        //have the caller execute the cross-tab query
        SqlContext.Pipe.ExecuteAndSend(command);

        //close the connection
        command.Connection.Close();
    }
};

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