IF EXISTS (SELECT name FROM sysobjects where name = 'p_get_hps_by_hpk ' and type = 'P ')
DROP PROCEDURE p_get_hps_by_hpk
go
create PROCEDURE p_get_hps_by_hpk(@hpzlid int,@hpkid int,@statusid int,@begindate varchar,@enddate varchar)
AS
declare @sql varchar(1000)
set @sql = 'select * from cg_hps where '
if @hpzlid <> 0
set @sql = @sql + 'hptype_id= ' + str(@hpzlid)
if @hpkid <> 0
set @sql = @sql + ' and hpk_id= ' + str(@hpkid)
if @statusid <> 0
set @sql = @sql + ' and hpstatus_id= ' + str(@statusid)
if @begindate <> ' '
set @sql = @sql + ' and created_at between ' + @begindate + ' and ' + @enddate + ' '
print @sql
--execute(@sql)
go
execute p_get_hps_by_hpk 103,501,0, '1982-1-11 ', '1910-11-11 '
------解决方案--------------------
IF EXISTS (SELECT name FROM sysobjects where name = 'p_get_hps_by_hpk ' and type = 'P ')
DROP PROCEDURE p_get_hps_by_hpk
go
create PROCEDURE p_get_hps_by_hpk(@hpzlid int,@hpkid int,@statusid int,@begindate varchar(10),@enddate varchar(10))
AS
declare @sql varchar(1000)
set @sql = 'select * from cg_hps where 1=1 '
if @hpzlid <> 0
set @sql = @sql + ' and hptype_id= ' + ltrim(@hpzlid)
if @hpkid <> 0
set @sql = @sql + ' and hpk_id= ' + ltrim(@hpkid)
if @statusid <> 0
set @sql = @sql + ' and hpstatus_id= ' + ltrim(@statusid)
if @begindate <> ' '
set @sql = @sql + ' and created_at between ' ' ' + @begindate + ' ' ' and ' ' ' + @enddate + ' ' ' '
print @sql
--execute(@sql)
go
execute p_get_hps_by_hpk 103,501,0, '1982-1-11 ', '1910-11-11 '
--result
select * from cg_hps where 1=1 and hptype_id=103 and hpk_id=501 and created_at between '1982-1-11 ' and '1910-11-11 '