各位大侠:
我从昨天开始一直困饶的一个存储过程,程序如下:
CREATE procedure [dbo].[pro_GetInitiBataManageViewData]
(
@startIndex int,--开始的索引页
@endIndex int,--结束的索引页
@filedwhere varchar(50),--索引的字段
@strwhere varchar(50)--条件
)
as
set nocount on
begin
select * from (select top(@endIndex)+ row_number() over(order by c.BatchId) rn, c.LogId,d.JobName,c.BatchId,c.BatchName,e.ServerName,c.Status ,c.Workflow,c.ImageCount,c.LoadDateTime,c.LastDateTime,
LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )/86400))+'D '+
LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400/3600))+'H '+
LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400%3600/60))+'M '
as UseTime,
c.Comments
from
(select a.LogId,a.BatchId,a.BatchName,a.JobId,a.ServerId,a.Status,
a.Workflow,a.ImageCount,b.LoadDateTime,a.LastDateTime,a.Comments from dbo.tbl_BatchesLog a
inner join
(select LogId,min(LastDateTime) LoadDateTime, max(LastDateTime) lastDatetime
from tbl_BatchesLog
group by LogId) b
on a.LogId=b.LogId and a.LastDateTime=b.lastDatetime) c
Inner join tbl_Jobs d On c.JobId=d.JobId
Inner join tbl_ServersInfo e On c.ServerId=e.ServerId and @filedwhere = @strwhere ) t where rn>=@startIndex
end
这是一个分页的存储过程,@fieldwhere是一个字段,编译的时候,是没有问题的,但是当输入参数的时候,比如:
[pro_GetInitiBataManageViewData] 6,12,'c.status','CheckPosted',却没有结果,如果我直接用select查询的话,是没有问题的,可以得到结果,为什么我用参数代替这个字段,就会得不到呢,请各位高手指点明精。
我从昨天开始一直困饶的一个存储过程,程序如下:
CREATE procedure [dbo].[pro_GetInitiBataManageViewData]
(
@startIndex int,--开始的索引页
@endIndex int,--结束的索引页
@filedwhere varchar(50),--索引的字段
@strwhere varchar(50)--条件
)
as
set nocount on
begin
select * from (select top(@endIndex)+ row_number() over(order by c.BatchId) rn, c.LogId,d.JobName,c.BatchId,c.BatchName,e.ServerName,c.Status ,c.Workflow,c.ImageCount,c.LoadDateTime,c.LastDateTime,
LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )/86400))+'D '+
LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400/3600))+'H '+
LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400%3600/60))+'M '
as UseTime,
c.Comments
from
(select a.LogId,a.BatchId,a.BatchName,a.JobId,a.ServerId,a.Status,
a.Workflow,a.ImageCount,b.LoadDateTime,a.LastDateTime,a.Comments from dbo.tbl_BatchesLog a
inner join
(select LogId,min(LastDateTime) LoadDateTime, max(LastDateTime) lastDatetime
from tbl_BatchesLog
group by LogId) b
on a.LogId=b.LogId and a.LastDateTime=b.lastDatetime) c
Inner join tbl_Jobs d On c.JobId=d.JobId
Inner join tbl_ServersInfo e On c.ServerId=e.ServerId and @filedwhere = @strwhere ) t where rn>=@startIndex
end
这是一个分页的存储过程,@fieldwhere是一个字段,编译的时候,是没有问题的,但是当输入参数的时候,比如:
[pro_GetInitiBataManageViewData] 6,12,'c.status','CheckPosted',却没有结果,如果我直接用select查询的话,是没有问题的,可以得到结果,为什么我用参数代替这个字段,就会得不到呢,请各位高手指点明精。
declare @sql nvarchar(max)
set @sql = '你的语句,单引号用双引号替换,'+@变量 + '你的语句,单引号用双引号替换'
exec @sql