请问在页面传值后 存储,执行存储过程怎么不能查询到数据呢.我知道是"'"号的转义的问题.但是想了好多办法没解决掉,求助达人
谢谢!
@dtmStartTime nvarchar(20),
@dtmEndTime nvarchar(20),
@intUserType int
AS
declare @chvnSql NVARCHAR(1000)
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
--查询条件
IF(LEN(@dtmStartTime)>0 or LEN(@dtmEndTime)>0)
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
IF(@intUserType>0)
set @chvnSql=@chvnSql+ ' and [userType] ='+STR(@intUserType)
EXECUTE sp_executesql @chvnSql
GO
sql 语句可以查询到:
SELECT * FROM pageVisits where [userVisitTime] between '2008-7-4' and '2008-7-28' and [userType] =3
谢谢!
@dtmStartTime nvarchar(20),
@dtmEndTime nvarchar(20),
@intUserType int
AS
declare @chvnSql NVARCHAR(1000)
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
--查询条件
IF(LEN(@dtmStartTime)>0 or LEN(@dtmEndTime)>0)
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
IF(@intUserType>0)
set @chvnSql=@chvnSql+ ' and [userType] ='+STR(@intUserType)
EXECUTE sp_executesql @chvnSql
GO
sql 语句可以查询到:
SELECT * FROM pageVisits where [userVisitTime] between '2008-7-4' and '2008-7-28' and [userType] =3
2.Str(@intUserType)==》Ltrim(Str(@intUserType))
如果
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
这个不满足的话,
那么你的语句可能就是这样的了,
SELECT id,Url,from,userName,userType FROM 表名 and [userVisitTime] between '2008-7-4' and '2008-7-28' and [userType] =3
这样肯定是有问题的,
建议把这部分
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
修改为
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 WHERE 1=1 ' -- 加上WHERE 1=1 IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @sqlWhere=@sqlWhere+' AND id>0 ' -- 去掉WHERE ,加上 AND
在试试
我原来碰到此问题,后面用sp_executesql带参数传入的方法才得以解决。
原因我认为是 datetime在拼字符串的时候转成了nvarchar类型,结果转不回来datetime型。
@dtmStartTime nvarchar(20),
@dtmEndTime nvarchar(20),
@intUserType int
AS
declare @chvnSql NVARCHAR(1000)
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
--查询条件
IF(LEN(@dtmStartTime)>0 or LEN(@dtmEndTime)>0)
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
IF(@intUserType>0)
set @chvnSql=@chvnSql+ ' and [userType] ='+STR(@intUserType)
print @chvnSql
-- EXECUTE sp_executesql @chvnSql
GO
print @chvnSql
declare @dtmStartTime nvarchar(20),
@dtmEndTime nvarchar(20),
@intUserType int
select @dtmStartTime=getdate(),@dtmEndTime=getdate(),@intUserType=1
declare @chvnSql NVARCHAR(1000)
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
--查询条件
IF(LEN(@dtmStartTime)>0 or LEN(@dtmEndTime)>0)
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
IF(@intUserType>0)
set @chvnSql=@chvnSql+ ' and [userType] ='+STR(@intUserType)
print @chvnSql
GO结果
SELECT id,Url,from,userName,userType FROM 表名 where id>0 and [userVisitTime] between convert(datetime,'07 28 2008 1:00PM') and convert(datetime,'07 28 2008 1:00PM') and [userType] = 1上面是我print的 楼主测试一下啊自己修改
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')' 参数同名了 当然没数据了 都是开始时间 @dtmStartTime
给分了 哈哈