如何在cs页面中调用一个sql 函数？

[wangxiaofeiwuqiao]

函数如下：

--创建函数实现code的合并
create function dbo.f_str(@ITEM_CODE varchar(20),@des varchar(20) ,@date_num int, @dt datetime) 

returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cast(code as varchar) from 
  (
    select distinct t1.* , t3.CODE from
    (
      SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff

(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end date_num FROM 

[VIEW_DS_MOTime] m group by m.ITEM_CODE,m.DES,case when datediff(week,@dt,m.date_start) + 1 <= 0 

then 0 else datediff(week,@dt,m.date_start) + 1 end
    ) t1,VIEW_DS_MOTime t2, VIEW_SO_LN1 t3
    where t1.ITEM_CODE = t2.ITEM_CODE and t2.SO_NUMBER = t3.INVOICE
    and (case when datediff(week,@dt,t2.date_start) + 1 <= 0 then 0 else datediff

(week,@dt,t2.date_start) + 1 end) = t1.date_num
  ) o
  where ITEM_CODE = @ITEM_CODE and des = @des and date_num = @date_num
  return @str
end
go

--定义日期以决定当周
declare @dt as datetime
set @dt = '2010-11-06'

select ITEM_CODE,des,
       max(case date_num when 0 then ltrim(to_be_fllw) else '' end) [0wk],
       max(case date_num when 0 then code else '' end) [0wk需求成品],
       max(case date_num when 1 then ltrim(to_be_fllw) else '' end) [1wk],
       max(case date_num when 1 then code else '' end) [1wk需求成品],
       max(case date_num when 2 then ltrim(to_be_fllw) else '' end) [2wk],
       max(case date_num when 2 then code else '' end) [2wk需求成品],
       max(case date_num when 3 then ltrim(to_be_fllw) else '' end) [3wk],
       max(case date_num when 3 then code else '' end) [3wk需求成品],
       max(case date_num when 4 then ltrim(to_be_fllw) else '' end) [4wk],
       max(case date_num when 4 then code else '' end) [4wk需求成品],
       max(case date_num when 5 then ltrim(to_be_fllw) else '' end) [5wk],
       max(case date_num when 5 then code else '' end) [5wk需求成品],
       max(case date_num when 6 then ltrim(to_be_fllw) else '' end) [6wk],
       max(case date_num when 6 then code else '' end) [6wk需求成品],
       max(case date_num when 7 then ltrim(to_be_fllw) else '' end) [7wk],
       max(case date_num when 7 then code else '' end) [7wk需求成品],
       max(case date_num when 8 then ltrim(to_be_fllw) else '' end) [8wk],
       max(case date_num when 8 then code else '' end) [8wk需求成品],
       max(case date_num when 9 then ltrim(to_be_fllw) else '' end) [9wk],
       max(case date_num when 9 then code else '' end) [9wk需求成品],
       max(case date_num when 10 then ltrim(to_be_fllw) else '' end) [10wk],
       max(case date_num when 10 then code else '' end) [10wk需求成品],
       max(case date_num when 11 then ltrim(to_be_fllw) else '' end) [11wk],
       max(case date_num when 11 then code else '' end) [11wk需求成品],
       max(case date_num when 12 then ltrim(to_be_fllw) else '' end) [12wk],
       max(case date_num when 12 then code else '' end) [12wk需求成品]
from
(
select ITEM_CODE , des , date_num , max(to_be_fllw) to_be_fllw, code = dbo.f_str(ITEM_CODE , des , 

date_num,@dt) from 
(
select distinct t1.* , t3.CODE from
(
SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff(week,@dt,m.date_start) + 

1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end date_num FROM [VIEW_DS_MOTime] m group 

by m.ITEM_CODE,m.DES,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff

(week,@dt,m.date_start) + 1 end
) t1,VIEW_DS_MOTime t2, VIEW_SO_LN1 t3
where t1.ITEM_CODE = t2.ITEM_CODE and t2.SO_NUMBER = t3.INVOICE
and (case when datediff(week,@dt,t2.date_start) + 1 <= 0 then 0 else datediff

(week,@dt,t2.date_start) + 1 end) = t1.date_num
) o
group by ITEM_CODE , des , date_num
) k
group by ITEM_CODE,des

我要怎么在cs页面中调用呢？因为我要用gridview控件显示出来，请给出具体方法

[wangxiaofeiwuqiao]

函数是没问题的

[q107770540]

这有个例子
你参考一下
http://www.cnblogs.com/geovindu/archive/2010/03/19/1689887.html

[q107770540]

SqlConnection conn = new SqlConnection(connectionString);
8 string strSql = "SQL函數名称"; //自定SQL函數

[joefly1943]

SqlConnection conn = new SqlConnection(connectionString);
8 string strSql = "SQL函數名称"; //自定SQL函數

[wangxiaofeiwuqiao]

谢谢楼上， 我想直接调用存储过程，要怎么办？

[wangxiaofeiwuqiao]

当我将它变成自定义函数的时候，好像sql的内容少了很多，是怎么回事？

[wangxiaofeiwuqiao]

那个函数在查询分析器里面是完全没问题的，但是我调用的时候一片空白，我怀疑是参数的问题，但是
create function dbo.f_str(@ITEM_CODE varchar(20),@des varchar(20) ,@date_num int, @dt datetime)
这里要写什么参数呢， 我的都是动态的

[zhang523012313]

你是怎样传入参数调用的哈
能写出来我看哈嘛

[wangxiaofeiwuqiao]

private void FileNo()
    {
        string ConnectionOledb = System.Configuration.ConfigurationManager.AppSettings["ConStringOledb"];
        OleDbConnection conn = new OleDbConnection(ConnectionOledb);
        string strSql = "f_str"; //自定SQL函數
        OleDbCommand cmd = new OleDbCommand(strSql, conn);
        cmd.CommandType = CommandType.StoredProcedure;
        //cmd.Parameters.Add("@Str", OleDbType.VarChar).Value = "ZQ3"; //輸入參數
        cmd.Parameters.Add("@ITEM_CODE", OleDbType.VarChar).Value = ""; //輸入參數
        cmd.Parameters.Add("@des", OleDbType.VarChar).Value = ""; //輸入參數
        cmd.Parameters.Add("@date_num", OleDbType.Integer).Value = 0; //輸入參數
        cmd.Parameters.Add("@dt", OleDbType.DBDate).Value = System.DateTime.Now.ToShortDateString(); //輸入參數

        cmd.Parameters.Add("@returnString", OleDbType.VarChar);
        cmd.Parameters["@returnString"].Direction = ParameterDirection.ReturnValue;  //返回參數 
        try
        {
            conn.Open();
            object o = cmd.ExecuteScalar();

            //this.txtAFileNO.Text = cmd.Parameters["@returnString"].Value.ToString();

            //Response.Write("");
            string strq = cmd.Parameters["@returnString"].Value.ToString();
            DataSet ds = Base.GetDataSet(strq,"ds");

            GridView1.DataSource = ds.Tables["ds"];
            // GridView1.DataKeyNames = new string[] { "Monday" };
            GridView1.DataBind();
        }
        catch (Exception ex)
        {
            //this.txtAFileNO.Text = ex.Message;
        }
        finally
        {
            if (!(conn.State == ConnectionState.Closed))
            {
                conn.Close();
            }
        }
    }

[种草德鲁伊]

当然是用SqlCommand...

[wangxiaofeiwuqiao]

用查询分析器直接运行不用写参数
但是我要在cs页面调用，有4个参数，(@ITEM_CODE varchar(20),@des varchar(20) ,@date_num int, @dt datetime) ，

[Teng_s2000]

提供CommandType.StoredProcedure就可以调用存储过程了啊
CommandType.Text就是调用表,默认

[whq0527]

调用存储过程要加一句代码：sqlcmd.CommandText="存储过程名";
添加参数一般用AddWithValue

[wangxiaofeiwuqiao]

问题是怎么把我的帖子那个函数转为可以直接调用的存储过程？十万火急啊

[wangxiaofeiwuqiao]

那位大哥帮弄弄，我实在弄不出来呀

[种草德鲁伊]

不用转也行的,一样能参数化执行

string sql = "select dbo.f_str(@ITEM_CODE, @des, @date_num, @dt)";

[wangxiaofeiwuqiao]

[Quote=引用 16 楼 karascanvas 的回复:]
不用转也行的,一样能参数化执行

string sql = "select dbo.f_str(@ITEM_CODE, @des, @date_num, @dt)";
[/Quote]
能否具体点
还有
为什么我把我帖子的内容保存为自定义函数的时候，好像
select ITEM_CODE,des,
max(case date_num when 0 then ltrim(to_be_fllw) else '' end) [0wk],
max(case date_num when 0 then code else '' end) [0wk需求成品],
max(case date_num when 1 then ltrim(to_be_fllw) else '' end) [1wk],
max(case date_num when 1 then code else '' end) [1wk需求成品],
max(case date_num when 2 then ltrim(to_be_fllw) else '' end) [2wk],
max(case date_num when 2 then code else '' end) [2wk需求成品],
max(case date_num when 3 then ltrim(to_be_fllw) else '' end) [3wk],
max(case date_num when 3 then code else '' end) [3wk需求成品],
max(case date_num when 4 then ltrim(to_be_fllw) else '' end) [4wk],
max(case date_num when 4 then code else '' end) [4wk需求成品],
max(case date_num when 5 then ltrim(to_be_fllw) else '' end) [5wk],
max(case date_num when 5 then code else '' end) [5wk需求成品],
max(case date_num when 6 then ltrim(to_be_fllw) else '' end) [6wk],
max(case date_num when 6 then code else '' end) [6wk需求成品],
max(case date_num when 7 then ltrim(to_be_fllw) else '' end) [7wk],
max(case date_num when 7 then code else '' end) [7wk需求成品],
max(case date_num when 8 then ltrim(to_be_fllw) else '' end) [8wk],
max(case date_num when 8 then code else '' end) [8wk需求成品],
max(case date_num when 9 then ltrim(to_be_fllw) else '' end) [9wk],
max(case date_num when 9 then code else '' end) [9wk需求成品],
max(case date_num when 10 then ltrim(to_be_fllw) else '' end) [10wk],
max(case date_num when 10 then code else '' end) [10wk需求成品],
max(case date_num when 11 then ltrim(to_be_fllw) else '' end) [11wk],
max(case date_num when 11 then code else '' end) [11wk需求成品],
max(case date_num when 12 then ltrim(to_be_fllw) else '' end) [12wk],
max(case date_num when 12 then code else '' end) [12wk需求成品]
from
(

这部分的东西都不见了

[wangxiaofeiwuqiao]

我明白了，前半部分是函数，后面是存储过程，存储过程调用那个函数

[lqkankan]

我是来看楼主头像的~