求人写一个SQL存储过程，该怎么解决
求人写一个SQL存储过程，
有如下语句
select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and czbt2.hnxc.czbt_btdj.lxdm in( '0501 ', '0502 ', '0503 ')
and month(djrq)=3
group by yhzh,object_name,gh,idcard_num
order by gh
其中and czbt2.hnxc.czbt_btdj.lxdm in( '0501 ', '0502 ', '0503 ')和
and month(djrq)=3
两个条件为不确定条件， 高分请高手帮忙！
------解决方案--------------------
create procedure sp_test
@lxdm varchar(40),
@month int
as
begin
declare @sql varchar(8000)
set @sql= 'select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' ' ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and czbt2.hnxc.czbt_btdj.lxdm in( '+@lxdm+ ')
and month(djrq)= '+rtrim(@month)+ '
group by yhzh,object_name,gh,idcard_num
order by gh '
exec(@sql)
end
go
exec sp_test ' ' '0501 ' ', ' '0502 ' ', ' '0503 ' ' ',3
exec sp_test ' ' '0541 ' ', ' '0542 ' ', ' '0513 ' ' ',11
go
------解决方案--------------------
create proc test_p @lxdm varchar(1000),@month int
as
select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and charindex( ', '+czbt2.hnxc.czbt_btdj.lxdm+ ', ', ', '+@lxdm+ ', ')> 0
and month(djrq)=@month
group by yhzh,object_name,gh,idcard_num
order by gh
------解决方案--------------------
Create ProceDure SP_TEST(@lxdm Varchar(100), @djrq Int)
As
Begin
select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and CharIndex(czbt2.hnxc.czbt_btdj.lxdm, @lxdm) > 0
and month(djrq)=@djrq
group by yhzh,object_name,gh,idcard_num
order by gh
End
有如下语句
select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and czbt2.hnxc.czbt_btdj.lxdm in( '0501 ', '0502 ', '0503 ')
and month(djrq)=3
group by yhzh,object_name,gh,idcard_num
order by gh
其中and czbt2.hnxc.czbt_btdj.lxdm in( '0501 ', '0502 ', '0503 ')和
and month(djrq)=3
两个条件为不确定条件， 高分请高手帮忙！
------解决方案--------------------
create procedure sp_test
@lxdm varchar(40),
@month int
as
begin
declare @sql varchar(8000)
set @sql= 'select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' ' ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and czbt2.hnxc.czbt_btdj.lxdm in( '+@lxdm+ ')
and month(djrq)= '+rtrim(@month)+ '
group by yhzh,object_name,gh,idcard_num
order by gh '
exec(@sql)
end
go
exec sp_test ' ' '0501 ' ', ' '0502 ' ', ' '0503 ' ' ',3
exec sp_test ' ' '0541 ' ', ' '0542 ' ', ' '0513 ' ' ',11
go
------解决方案--------------------
create proc test_p @lxdm varchar(1000),@month int
as
select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and charindex( ', '+czbt2.hnxc.czbt_btdj.lxdm+ ', ', ', '+@lxdm+ ', ')> 0
and month(djrq)=@month
group by yhzh,object_name,gh,idcard_num
order by gh
------解决方案--------------------
Create ProceDure SP_TEST(@lxdm Varchar(100), @djrq Int)
As
Begin
select cast(yhzh as char(20))+cast(object_name as char(10))+cast(gh as char(10))+cast(1 as char(10))+cast(idcard_num as char(20))+cast(sum(btje) as char(16))+ ' '
from czbt2.hnxc.czbt_btdj,t_sn_object,t_sn_object_zhxx
where t_sn_object.id=t_sn_object_zhxx.btdx_uid
and t_sn_object.id=czbt2.hnxc.czbt_btdj.btdx_uid
and CharIndex(czbt2.hnxc.czbt_btdj.lxdm, @lxdm) > 0
and month(djrq)=@djrq
group by yhzh,object_name,gh,idcard_num
order by gh
End