有人对sqlserver存储过程熟悉的吗,我想问个问题,因为我的查询参数不确定,不能直接查询,需要把datetime类型字段作为变量传入, 我的测试程序如下： create proc dbo.test @BeginTime datetime as begin declare @t_sql varchar(500) SET NOCOUNT ON set @t_sql='select O.object_id,T_LOGIN from R_AGENT_HOUR R,O_AGENT_HOUR O,T_AGENT_HOUR T where R.object_id=O.object_id and R.time_key=T.time_key and T.begin_time <' set @t_sql=@t_sql+' and T.begin_time <' select @t_sql --exec(@t_sql+cast(@BeginTime as varchar)) exec(@t_sql+@BeginTime) end
create proc dbo.test @BeginTime datetime as begin declare @t_sql varchar(500) SET NOCOUNT ON set @t_sql='select O.object_id,T_LOGIN from R_AGENT_HOUR R,O_AGENT_HOUR O,T_AGENT_HOUR T where R.object_id=O.object_id and R.time_key=T.time_key and T.begin_time <' set @t_sql=@t_sql+' and T.begin_time <' select @t_sql --exec(@t_sql+cast(@BeginTime as varchar)) exec(@t_sql+@BeginTime) end
alterproc dbo.test
@BeginTimedatetimeasbegindeclare@t_sqlvarchar(500)
SET NOCOUNT ONset@t_sql='select O.object_id,T_LOGIN
from R_AGENT_HOUR R,O_AGENT_HOUR O,T_AGENT_HOUR T
where R.object_id=O.object_id and R.time_key=T.time_key
and T.begin_time ='''+convert(varchar(20),@BeginTime,120)+''''exec(@t_sql)
end
createproc dbo.test
@BeginTimedatetimeasbegindeclare@t_sqlvarchar(500)
SET NOCOUNT ONset@t_sql='select O.object_id,T_LOGIN
from R_AGENT_HOUR R,O_AGENT_HOUR O,T_AGENT_HOUR T
where R.object_id=O.object_id and R.time_key=T.time_key and T.begin_time <'set@t_sql=@t_sql+' and T.begin_time <'''+ltrim(convert(nvarchar(10),@BeginTime,112))+''''exec(@t_sql)
end
createproc dbo.test
@BeginTimedatetimeasbegindeclare@t_sqlvarchar(500)
SET NOCOUNT ONset@t_sql='select O.object_id,T_LOGIN
from R_AGENT_HOUR R,O_AGENT_HOUR O,T_AGENT_HOUR T
where R.object_id=O.object_id and R.time_key=T.time_key '--去掉这里的begin_time set@t_sql=@t_sql+' and T.begin_time <'''+ltrim(convert(nvarchar(10),@BeginTime,112))+''''exec(@t_sql)
end
go insert into jj select '2008010101','2008-01-01',100 union all select '2008010102','2008-01-01',50 union all select '2008010103','2008-01-01',60 union all select '2008010201','2008-01-02',60 union all select '2008010301','2008-01-03',60 union all select '2008010401','2008-01-04',30
--------------- create proc dbo.test @BeginTime datetime as begin declare @t_sql varchar(500) SET NOCOUNT ON set @t_sql='select * from jj where dtime <' + char(39) + convert(varchar(10),@begintime,120) + char(39) select @t_sql --exec(@t_sql+cast(@BeginTime as varchar)) exec(@t_sql) end
