set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_GetLogList]
@groupID int ,
@date nvarchar(20),
@startIndex int,
@endIndex int
AS
IF @groupID=0
BEGIN
DECLARE @sql_Admin nvarchar(4000)
SET @sql_Admin='
SELECT * FROM (
SELECT c.*,d.sNewsOfficeName,Row_number() over(order by c.dClock desc) as Row FROM
(SELECT a.[iTransID]
,a.[iUserID]
,b.sUserName
,a.[sLog]
,a.[dClock]
,a.[sHostIP]
,a.[iGroupID]
FROM [t_TransactionLog] AS a,t_UserInfo AS b WHERE a.iUserID=b.iUserID) AS c,t_NewsPaperOffice AS d WHERE c.iGroupID = d.iNewsOfficeID) AS h
WHERE h.Row BETWEEN '+ cast (@startIndex AS nvarchar)+' AND '+ cast (@endIndex AS nvarchar)
IF @date=''
EXEC(@sql_Admin)
ELSE
BEGIN
SET @sql_Admin = @sql_Admin +' and CONVERT(nvarchar,h.dClock,23)='+cast( @date AS nvarchar)
EXEC(@sql_Admin)
END
END
ELSE
BEGIN
DECLARE @sql_NewsOffice nvarchar(4000)
SET @sql_NewsOffice='
SELECT * FROM
(SELECT c.*,d.sNewsOfficeName,Row_number() over(order by c.dClock desc) as Row FROM
(SELECT a.[iTransID],a.[iUserID],b.sUserName,a.[sLog],a.[dClock],a.[sHostIP],a.[iGroupID]
FROM [t_TransactionLog] AS a,t_UserInfo AS b
WHERE a.iUserID=b.iUserID AND a.iGroupID='+cast (@groupID AS nvarchar)+') AS c,t_NewsPaperOffice AS d WHERE c.iGroupID=d.iNewsOfficeID)AS h
WHERE h.Row BETWEEN '+ cast (@startIndex AS nvarchar)+' AND '+ cast( @endIndex AS nvarchar)
IF @date=''
EXEC(@sql_NewsOffice)
ELSE
BEGIN
SET @sql_NewsOffice =@sql_NewsOffice+' and CONVERT(nvarchar,h.dClock,23)=' + cast( @date AS nvarchar)
EXEC(@sql_NewsOffice)
END
END
执行后报错为消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 '2009-06-10' 转换成数据类型 int 时失败。请高手看看帮忙分析下是什么问题造成
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_GetLogList]
@groupID int ,
@date nvarchar(20),
@startIndex int,
@endIndex int
AS
IF @groupID=0
BEGIN
DECLARE @sql_Admin nvarchar(4000)
SET @sql_Admin='
SELECT * FROM (
SELECT c.*,d.sNewsOfficeName,Row_number() over(order by c.dClock desc) as Row FROM
(SELECT a.[iTransID]
,a.[iUserID]
,b.sUserName
,a.[sLog]
,a.[dClock]
,a.[sHostIP]
,a.[iGroupID]
FROM [t_TransactionLog] AS a,t_UserInfo AS b WHERE a.iUserID=b.iUserID) AS c,t_NewsPaperOffice AS d WHERE c.iGroupID = d.iNewsOfficeID) AS h
WHERE h.Row BETWEEN '+ cast (@startIndex AS nvarchar)+' AND '+ cast (@endIndex AS nvarchar)
IF @date=''
EXEC(@sql_Admin)
ELSE
BEGIN
SET @sql_Admin = @sql_Admin +' and CONVERT(nvarchar,h.dClock,23)='+cast( @date AS nvarchar)
EXEC(@sql_Admin)
END
END
ELSE
BEGIN
DECLARE @sql_NewsOffice nvarchar(4000)
SET @sql_NewsOffice='
SELECT * FROM
(SELECT c.*,d.sNewsOfficeName,Row_number() over(order by c.dClock desc) as Row FROM
(SELECT a.[iTransID],a.[iUserID],b.sUserName,a.[sLog],a.[dClock],a.[sHostIP],a.[iGroupID]
FROM [t_TransactionLog] AS a,t_UserInfo AS b
WHERE a.iUserID=b.iUserID AND a.iGroupID='+cast (@groupID AS nvarchar)+') AS c,t_NewsPaperOffice AS d WHERE c.iGroupID=d.iNewsOfficeID)AS h
WHERE h.Row BETWEEN '+ cast (@startIndex AS nvarchar)+' AND '+ cast( @endIndex AS nvarchar)
IF @date=''
EXEC(@sql_NewsOffice)
ELSE
BEGIN
SET @sql_NewsOffice =@sql_NewsOffice+' and CONVERT(nvarchar,h.dClock,23)=' + cast( @date AS nvarchar)
EXEC(@sql_NewsOffice)
END
END
执行后报错为消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 '2009-06-10' 转换成数据类型 int 时失败。请高手看看帮忙分析下是什么问题造成
解决方案 »
- 急!我的数据到excel里变成了小数,另外时间字段读取到gridview说datetime数据越界?
- 有关用asp.net ajax 的timer 控件实现局部更新的问题
- DataList中如何指定前几列的颜色?
- 问个DATAGRID的简单问题
- 从大到小输出X,Y,Z的值?
- 向数据库插入数据后,如何返回插入数据所在的标示值?
- 大家新年好!有一问题请教…… (关于FindControl)
- 关于PAGE_load事件的激发??????
- 怎样在datagrid中增添一个自增的列?
- 我的vs.net怎么不能新建asp.net web应用程序?
- 如何在框架中打开网页
- 请问“asp.net中脚本中获得本地路径的问题?”
cast( @date AS nvarchar)
这些转换都是不必要的