IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'procGudChe' AND type = 'P')
DROP PROCEDURE procGudChe
GO
create proc procGudChe
@chep varchar(20)=null,@chex varchar(20)=null,@siji varchar(20)=null,@yanse varchar(20)=null,
@shenfz varchar(20)=null,@banzy varchar(20)=null,@rumg varchar(20)=null,@chumg varchar(20)=null,
@beginTime varchar(20)=null,@endTime varchar(20)=null,@yousq varchar(20)=null
as
declare @sql varchar(8000)set @sql = ''
if @chep is not null set @sql = sql + 'chepai='''+@chep +''' and '
if @chep is not null set @sql = sql + 'chepai='''+@chep +''' and '
......exec ('select * from Acheliang where '+ @sql + ' kaleix=''固定卡''')
WHERE name = 'procGudChe' AND type = 'P')
DROP PROCEDURE procGudChe
GO
create proc procGudChe
@chep varchar(20)=null,@chex varchar(20)=null,@siji varchar(20)=null,@yanse varchar(20)=null,
@shenfz varchar(20)=null,@banzy varchar(20)=null,@rumg varchar(20)=null,@chumg varchar(20)=null,
@beginTime varchar(20)=null,@endTime varchar(20)=null,@yousq varchar(20)=null
as
declare @sql varchar(8000)set @sql = ''
if @chep is not null set @sql = sql + 'chepai='''+@chep +''' and '
if @chep is not null set @sql = sql + 'chepai='''+@chep +''' and '
......exec ('select * from Acheliang where '+ @sql + ' kaleix=''固定卡''')
方法1:
select *
from Acheliang
where
(chepai=@chep or isnull(@chep,'')='')
and 其他条件按上句的例子写
and kaleix='固定卡'方法二:
select *
from Acheliang
where
@chep = case when @chep is not null then '' else chepai end
and 其他条件按上句的例子写
and kaleix='固定卡'
WHERE name = 'procGudChe' AND type = 'P')
DROP PROCEDURE procGudChe
GO
create proc procGudChe
@chep varchar(20)=null,@chex varchar(20)=null,@siji varchar(20)=null,@yanse varchar(20)=null,
@shenfz varchar(20)=null,@banzy varchar(20)=null,@rumg varchar(20)=null,@chumg varchar(20)=null,
@beginTime varchar(20)=null,@endTime varchar(20)=null,@yousq varchar(20)=null
as
declare @sql varchar(8000)
set @sql = ''
if @chep is not null set @sql = @sql + 'chepai='''+@chep +''' and '
if @chex is not null set @sql = @sql + 'chexing='''+@chex +''' and '
if @siji is not null set @sql = @sql + 'siji='''+@siji +''' and '
if @yanse is not null set @sql = @sql + 'yanse='''+@yanse +''' and '
if @shenfz is not null set @sql = @sql + 'shenfz='''+@shenfz +''' and '
if @banzy is not null set @sql = @sql + 'banzy='''+@banzy +''' and '
if @rumg is not null set @sql = @sql + 'jinmk='''+@rumg +''' and '
if @chumg is not null set @sql = @sql + 'chumk='''+@chumg +''' and '
if @beginTime is not null set @sql = @sql + 'banzsj='''+@beginTime +''' and '
if @endTime is not null set @sql = @sql + 'banzsj='''+@endTime +''' and '
exec ('select * from Acheliang where '+ @sql + ' kaleix=''固定卡''')exec procGudChe
没有传任何参数的查询结果!
29 JKZ NULL NULL NULL 365 0 固定卡 NULL NULL NULL NULL NULL
13 豫E A0004 NULL NULL NULL NULL 固定卡 挂失卡 NULL NULL NULL NULL
15 豫E A0006 NULL NULL NULL NULL 固定卡 挂失卡 NULL NULL NULL NULL
如果我要传不同的参数,如果我只传@chep 和 @siji,怎么写???对了,我还有个问题,就是@beginTime,@endtime如果只传了一个参数,可以得到结果.如果两个都传了,需要把@beginTime和@endtime时间段内符合条件的查出来,我想在这个过程中写,怎么完成!??
happyflystone--固定车辆查询存储过程
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'procGudChe' AND type = 'P')
DROP PROCEDURE procGudChe
GO
create proc procGudChe
@chep varchar(20)=null,@chex varchar(20)=null,@siji varchar(20)=null,@yanse varchar(20)=null,
@shenfz varchar(20)=null,@banzy varchar(20)=null,@rumg varchar(20)=null,@chumg varchar(20)=null,
@beginTime varchar(20)=null,@endTime varchar(20)=null,@yousq varchar(20)=null
as
declare @sql varchar(8000)
set @sql = ''
if @chep is not null set @sql = @sql + 'chepai='''+@chep +''' and '
if @chex is not null set @sql = @sql + 'chexing='''+@chex +''' and '
if @siji is not null set @sql = @sql + 'siji='''+@siji +''' and '
if @yanse is not null set @sql = @sql + 'yanse='''+@yanse +''' and '
if @shenfz is not null set @sql = @sql + 'shenfz='''+@shenfz +''' and '
if @banzy is not null set @sql = @sql + 'banzy='''+@banzy +''' and '
if @rumg is not null set @sql = @sql + 'jinmk='''+@rumg +''' and '
if @chumg is not null set @sql = @sql + 'chumk='''+@chumg +''' and '
if @beginTime is not null and @endTime is not null
set @sql=@sql + 'banzsj between'''+(case @beginTime as datetime) +'''and'''+(cast @endTime as datetime)+'''and'
else
begin
if @beginTime is not null set @sql = @sql + 'banzsj='''+@beginTime +''' and '
if @endTime is not null set @sql = @sql + 'banzsj='''+@endTime +''' and '
end
exec ('select * from Acheliang where '+ @sql + ' kaleix=''固定卡''')
exec procGudChe null,null,null,null,null,null,null,null,'2007-09-14 16:37:16','2007-09-14 16:50:14'