有4个参数,@begintime,@endtime,@id,@name , 4个text控件txtbegintime,txtendtime,txtid,txtname ,3个字段time,id,name
要求:begintime---endtime时间段,以及符合@id,@name参数的记录(需要考虑参数传入值为空的情况)
求组合查询的存储过程,请各位给予帮助,万分感谢!
要求:begintime---endtime时间段,以及符合@id,@name参数的记录(需要考虑参数传入值为空的情况)
求组合查询的存储过程,请各位给予帮助,万分感谢!
@begintime datetime,
@endtime datetime,
@id int,
@name nvarchar(20)
as
set nocount on
declare @sql nvarchar(4000)
set @sql=' where time>='''+convert(varchar,@begintime,121)+''''
set @sql=isnull(@sql+' and', ' where')+' time<='''+convert(varchar,@endtime,121)+''''
set @sql=isnull(@sql+' and', ' where')+' id='+ltrim(@id)
set @sql=isnull(@sql+' and', ' where')+' name='''+@name+''''
exec ('select * from 表'+@sql)
set nocount off
@begintime datetime,
@endtime datetime,
@id int,
@name nvarchar(20)
as
set nocount on
declare @sql nvarchar(4000)
set @sql=' where time>='''+convert(varchar,@begintime,121)+''''
set @sql=isnull(@sql+' and', ' where')+' time<='''+convert(varchar,@endtime,121)+''''
set @sql=isnull(@sql+' and', ' where')+' id='+ltrim(@id)
set @sql=isnull(@sql+' and', ' where')+' name='''+@name+''''
set @sql=isnull(@sql,'')-->漏了这句
exec ('select * from 表'+isnull(@sql)
set nocount off
create proc pTest
@begintime datetime,
@endtime datetime,
@id int,
@name nvarchar(20)
as
set nocount on
declare @sql nvarchar(4000)
set @sql=' where time>='''+convert(varchar,@begintime,121)+''''
set @sql=isnull(@sql+' and', ' where')+' time<='''+convert(varchar,@endtime,121)+''''
set @sql=isnull(@sql+' and', ' where')+' id='+ltrim(@id)
set @sql=isnull(@sql+' and', ' where')+' name='''+@name+''''
set @sql=isnull(@sql,'')-->漏了这句
print ('select * from 表'+@sql)
set nocount off
print --> exec
create proc pTest
@begintime datetime=null,
@endtime datetime=null,
@id int=null,
@name nvarchar(20)=null
as
declare @sql nvarchar(4000)
set @sql=''
set @sql=@sql+isnull(' and id='+ltrim(@id),'')
set @sql=@sql+isnull(' and name='''+@name+'''','')
set @sql=@sql+isnull(' and time>='''+convert(varchar,@begintime,121)+'''','')
set @sql=@sql+isnull(' and time<='''+convert(varchar,@endtime,121)+'''','')
set @sql='select * from 表名 where 1=1'+@sql
exec(@sql)
--调用:
exec pTest @begintime='2008-03-21',@endtime='2008-03-21',@id=1
@begintime datetime=null,
@endtime datetime=null,
@id int=null,
@name nvarchar(20)=null
as
set nocount ondeclare @sql nvarchar(4000)
set @sql=''
set @sql=@sql+
isnull(' and time>='''+convert(varchar,@begintime,121)+'''', '')+
isnull(' and time<='''+convert(varchar,@endtime,121)+'''', '')+
isnull(' and id='+ltrim(@id), '')+
isnull(' and name='''+@name+'''', '')
set @sql=isnull(stuff(@sql,1,4,' where'), '')
print ('select * from 表'+@sql)set nocount off
drop table 表
if object_id('usp_test') is not null
drop proc usp_test
go
create table 表(time datetime,id int,name varchar(100))
insert into 表
select '2008-4-7',1,'名称1' union all
select '2008-4-6',2,'名称2' union all
select '2008-4-5',3,'名称3' union all
select '2008-4-4',4,'名称4' union all
select '2008-4-3',5,'名称5' union all
select '2008-4-2',6,'名称6' union all
select '2008-4-1',7,'名称7' union all
select '2008-3-31',8,'名称8' union all
select '2008-3-30',9,'名称9'
go
--usp_test '2008-4-1','2008-4-7',2,'名称2'
create proc usp_test
(
@begintime datetime=null,
@endtime datetime=null,
@id int=null,
@name varchar(100)=null
)
as
begin
declare @sql varchar(8000)
print @sql
set @sql=' where 1=1'
set @sql=isnull(@sql,'')+isnull(' and time>='''+convert(varchar(10),@begintime,120)+'''','')
set @sql=isnull(@sql,'')+isnull(' and time<='''+convert(varchar(10),@endtime,120)+'''','')
set @sql=isnull(@sql,'')+isnull(' and id='''+cast(@id as varchar(100))+'''','')
set @sql=isnull(@sql,'')+isnull(' and name='''+cast(@name as varchar(100))+'''','')
set @sql='select * from 表'+@sql
print @sql
exec(@sql)
end
go
如:查询2008-04-01的数据,参数值如下
@begintime @endtime
2008-04-06 --- 2008-04-06
查询不到记录
2008-04-05 --- 2008-04-07
这样才可以,是不是日期格式的问题,还是我用VB.NET datetimepicker控件的问题?请帮助,谢谢!
http://topic.csdn.net/u/20080227/14/8bce0844-bd15-42f0-9cda-a343d5d6601b.html