View Code

    class Sql
    {
        public DataTable getData(string sql)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Test;User Id=sa;Password=1;";
            SqlCommand comm = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "ds");
            conn.Close();
            return ds.Tables[0];
        }
    }

View Code

--根据课程ID，返回选此课程的学生的名字，以逗号隔开
ALTER function [dbo].[f_getStuNamesByClassID] (@ID int)
RETURNS nvarchar(50)
begin
    declare @Result nvarchar(50);
    declare @stuName nvarchar(50);
    Set @Result='';

    declare cur cursor for
    (
        SELECT S.StuName FROM Class C
        LEFT JOIN Student S ON C.ID=S.ClassID
        WHERE C.ID=@ID
    )
    open cur;
    fetch next from cur into @stuName;
    while(@@fetch_status=0)
    begin
        set @Result=@Result+@stuName+',';
        fetch next from cur into @stuName;
    end;
--去除最后多余的一个逗号
    IF @Result <> ''
        SET @Result=SUBSTRING(@Result, 1, LEN(@Result)-1);
    ELSE
        SET @Result=NULL;
    return @Result;
en