我的自定义函数是这样的:create function fn_getwk(
@year int,--输入显示的年份
@month int, --输入显示的月份
@type bit=0 --指定每周的第一天,默认为星期日(中国习惯),如果参数为1就是星期一为每周的第一天
) returns @t table(
周数 nvarchar(10),
开始日期 varchar(10),
结束日期 varchar(10)
) as
begin
declare @d datetime
set @d=dateadd(wk,datediff(wk,'1900',cast(ltrim(@year*10000+@month*100+1) as datetime)),'1900')+@type
;with t as
(
select top (datediff(dd,ltrim(@year*10000+@month*100+1),ltrim(@year*10000+(@month+1)*100+1)))
[date]=cast(ltrim(@year*10000+@month*100+1) as datetime)-1
+row_number()over(order by getdate())
from sysobjects
)
insert @t
select
case
when [date] between @d and @d+5 then '第一周'
when [date] between @d+6 and @d+12 then '第二周'
when [date] between @d+13 and @d+19 then '第三周'
when [date] between @d+20 and @d+26 then '第四周'
when [date] between @d+27 and @d+33 then '第五周'
else '第六周'
end AS 周数,
convert(varchar,min([date]),23) 开始日期,
convert(varchar,max([date]),23) 结束日期
from t
group by
case
when [date] between @d and @d+5 then '第一周'
when [date] between @d+6 and @d+12 then '第二周'
when [date] between @d+13 and @d+19 then '第三周'
when [date] between @d+20 and @d+26 then '第四周'
when [date] between @d+27 and @d+33 then '第五周'
else '第六周'
end
order by 2
return
end
GOselect * from fn_getwk(2010,5,1)select count(*) from fn_getwk(2010,5,1)
我想查询某个月中有几个周,返回个数:
比如:select count(*) from fn_getwk(2010,5,1),
但是这句话要在后台写,
我在后台用的时候怎么用啊???求顶 ,求说明~~~~~~ 急用~~~~
@year int,--输入显示的年份
@month int, --输入显示的月份
@type bit=0 --指定每周的第一天,默认为星期日(中国习惯),如果参数为1就是星期一为每周的第一天
) returns @t table(
周数 nvarchar(10),
开始日期 varchar(10),
结束日期 varchar(10)
) as
begin
declare @d datetime
set @d=dateadd(wk,datediff(wk,'1900',cast(ltrim(@year*10000+@month*100+1) as datetime)),'1900')+@type
;with t as
(
select top (datediff(dd,ltrim(@year*10000+@month*100+1),ltrim(@year*10000+(@month+1)*100+1)))
[date]=cast(ltrim(@year*10000+@month*100+1) as datetime)-1
+row_number()over(order by getdate())
from sysobjects
)
insert @t
select
case
when [date] between @d and @d+5 then '第一周'
when [date] between @d+6 and @d+12 then '第二周'
when [date] between @d+13 and @d+19 then '第三周'
when [date] between @d+20 and @d+26 then '第四周'
when [date] between @d+27 and @d+33 then '第五周'
else '第六周'
end AS 周数,
convert(varchar,min([date]),23) 开始日期,
convert(varchar,max([date]),23) 结束日期
from t
group by
case
when [date] between @d and @d+5 then '第一周'
when [date] between @d+6 and @d+12 then '第二周'
when [date] between @d+13 and @d+19 then '第三周'
when [date] between @d+20 and @d+26 then '第四周'
when [date] between @d+27 and @d+33 then '第五周'
else '第六周'
end
order by 2
return
end
GOselect * from fn_getwk(2010,5,1)select count(*) from fn_getwk(2010,5,1)
我想查询某个月中有几个周,返回个数:
比如:select count(*) from fn_getwk(2010,5,1),
但是这句话要在后台写,
我在后台用的时候怎么用啊???求顶 ,求说明~~~~~~ 急用~~~~
create proc Test
(
year varchar(10),
Months varchar(10),
Date varchar(10)
)
as
select count(*) from fn_getwk(year,Months,Date)
我知道调用没问题啊 ,这是在SQL里面, 我现在在后台怎么用? 直接写那句话报错啊 ...
===>就这么用啊.
像普通的sql一样用
/// 查询一月有几周
/// </summary>
/// <returns></returns>
public int SelectWeekCount(int yy,int mm,int dd) {
string sql = "select count(*) from fn_getwk("+yy+","+mm+","+dd+")";
int count = SqlHelper.ExecuteNonQuery(sql, null);
return count;
}
报的错误时:对象fn_getwk无效,但是这个函数我已经建好了,
SQL里面查没问题···· 如何解????
我加了,但是还错这个是现在的: /// <summary>
/// 查询一月有几周
/// </summary>
/// <returns></returns>
public int SelectWeekCount(int yy,int mm,int dd) {
string sql = "select count(*) from dbo.fn_getwk(" + yy + "," + mm + "," + dd + ")";
int count = SqlHelper.ExecuteNonQuery(sql, null);
return count;
}
有什么问题~~~
查询语句在SQL里面运行没有问题啊~~~~
很确定参数没问题,
我调试了,都走了 ,参数什么都有,得到的SQL我在数据库运行了,也可以查询,
但是 走到:
int count = SqlHelper.ExecuteNonQuery(sql, null);
就爆 :对象fn_getwk无效 我想不明白,获取到的SQL在数据库都执行查出来了,
在这里爆无效
string sql = "select count(*) from dbo.fn_getwk('" + yy + "','" + mm + "','" + dd + "')";
dbo.fn_getwk无效才对.
但是获取的sql在数据库执行正确,并且查出数据,怎么回事?
慕白大哥,不好意思啊, 打错了 就是dbo.fn_getwk无效 什么原因?
/// 查询一月有几周
/// </summary>
/// <returns></returns>
public int SelectWeekCount(int yy,int mm,int dd) {
string sql = "select count(*) from dbo.fn_getwk(" + yy + "," + mm + "," + dd + ")";
int count = SqlHelper.ExecuteNonQuery(sql, null);
return count;
}
这个是查询 ,你们看我调用的是什么?
int count = SqlHelper.ExecuteNonQuery(sql, null);
应该调用:
SqlHelper.ExecuteScalar(sql, null)一个手误引发的事件~~~~ 悲剧的我,悲剧的结贴了
SQL函数如下:/***********************************************************************
***********************************************************************/
CREATE FUNCTION GetChannel_SMSName(@ChannelID AS INT)
RETURNS varchar(30) AS
BEGIN
declare @sValue varchar(30)
set @sValue=''
SELECT @sValue=[Name]
FROM [dbo].[Channel_SMS]
WHERE ([ID] = @ChannelID)
return @sValue
END
程序中调用:
SqlDatabase db = new SqlDatabase();
db.DbConn.Open();
db.DbCmd.CommandText = "select [dbo].GetChannel_SMSName(ChannelID) AS ChannelName From UserWebHost where [UserId]='" + Session["EUserID"].ToString() + "'";
SqlDataReader dr = db.DbCmd.ExecuteReader();
db.DbConn.Close();
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/liushengpiaoxu/archive/2007/07/28/1713837.aspx
SQL函数如下CREATE FUNCTION GetChannel_SMSName(@ChannelID AS INT)
RETURNS varchar(30) AS
BEGIN
declare @sValue varchar(30)
set @sValue=''
SELECT @sValue=[Name]
FROM [dbo].[Channel_SMS]
WHERE ([ID] = @ChannelID)
return @sValue
END
程序中调用:
SqlDatabase db = new SqlDatabase();
db.DbConn.Open();
db.DbCmd.CommandText = "select [dbo].GetChannel_SMSName(ChannelID) AS ChannelName From UserWebHost where [UserId]='" + Session["EUserID"].ToString() + "'";
SqlDataReader dr = db.DbCmd.ExecuteReader();
db.DbConn.Close();
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/liushengpiaoxu/archive/2007/07/28/1713837.aspx