在存储过程中的like运算符中传递变量

本文介绍了在存储过程中的like运算符中传递变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

创建表#tempzeroapproved （ hub_name varchar（100）， address_id int， geocode_address varchar（300） ） 插入#tempzeroapproved（hub_name，address_id，geocode_address） SELECT tf .facility_name，tg.address_id，isnull（Landmark，''）+''+ isnull（Colony，''）+''+ isnull（Sub_Colony，''）+''+ isnull（CITY，''）as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg 加入tbl_user_master tum on tum.address_id = tg.address_id 加入tfl_facility tf on tf.facility_id = tum.facility_id WHERE tg.ADDRESS_ID NOT IN（从tbl_approved_distances选择TO_ID WHERE FROMID = 20）和mmim_key不是null 和tum.Facility_ID in（选择来自tbl_Facility的Facility_ID，其中Hub_ID = 20） 和tg.Hub_Ids如'％20％' UNION ALL SELECT tf.facility_name，tg.address_id，isnull（Landmark，''）+ '+ isnull（Colony，''）+''+ isnull（Sub_Colony，''）+''+ isnull（CITY，''）as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg 加入tbl_user_master tum on tum.address_id = tg.address_id 加入tbl_facility tf on tf.facility_id = tum.facility_id WHERE tg.ADDRESS_ID IN（选择TO_ID来自tbl_approved_distances WHERE FROMID = 20且距离= 0）且mmim_key不为空 和tum.Facility_ID in（选择来自tbl_Facility的Facility_ID，其中Hub_ID = 20） 和tg.Hub_Ids如'％20％' select hub_name，address_id，来自#tempzeroapproved的geocode_address ----现在上面的东西给了我输出成功--------- ------ ---场景 现在当我尝试在存储过程中传递参数20时...有问题......我没有得到输出 ---------------------- ------------ -------------------------------------------------- --- ALTER PROCEDURE APPROVED_KM - 20 （ @HUB_ID INT ） AS BEGIN 创建表#tempzeroapproved （ hub_name varchar（100）， address_id int， geocode_address varchar（300） ） 插入#tempzeroapproved（hub_name，address_id，geocode_address） SELECT tf.facility_name，tg.address_id，isnull（Landmark，''）+''+ isnull（Colony，' '）+''+ isnull（Sub_Colony，''）+''+ isnull（CITY，''）as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg 加入tbl_user_master tum on tum.address_id = tg.address_id 加入tbl_facility tf on tf.facility_id = tum.facility_id WHERE tg.ADDRESS_ID NOT IN（选择TO_ID来自tbl_approved_distances WHERE FROMID = @ HUB_ID）并且mmim_key不为空 和tum.Facili ty_ID in（选择来自tbl_Facility的Facility_ID，其中Hub_ID = @ HUB_ID） 和tg.Hub_Ids喜欢''％@ HUB_ID％'' UNION ALL SELECT tf.facility_name，tg.address_id，isnull（Landmark，''）+''+ isnull（Colony，''）+''+ isnull（Sub_Colony，''）+''+ isnull（CITY，'' ）as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg 加入tbl_user_master tum on tum.address_id = tg.address_id 加入tfl_facility tf on tf.facility_id = tum.facility_id WHERE tg.ADDRESS_ID IN（从tbl_approved_distances中选择TO_ID WHERE FROMID = @ HUB_ID和距离= 0）并且mmim_key不为空 和tum.Facility_ID in（选择来自tbl_Facility的Facility_ID，其中Hub_ID = @ HUB_ID） 和tg.Hub_Ids喜欢''％@ HUB_ID％'' - ''％'+ @ LastName +'％''' 选择hub_name，address_id，geocode_address from #tempzeroapproved 结束 ---如何通过这个20 -im做''％@ HUB_ID％'' - 没有结果

create table #tempzeroapproved ( hub_name varchar(100), address_id int, geocode_address varchar(300) ) insert into #tempzeroapproved(hub_name,address_id,geocode_address) SELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg join tbl_user_master tum on tum.address_id=tg.address_id join tbl_facility tf on tf.facility_id=tum.facility_id WHERE tg.ADDRESS_ID NOT IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=20) and mmim_key is not null and tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=20) and tg.Hub_Ids like '%20%' UNION ALL SELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg join tbl_user_master tum on tum.address_id=tg.address_id join tbl_facility tf on tf.facility_id=tum.facility_id WHERE tg.ADDRESS_ID IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=20 and Distance=0) and mmim_key is not null and tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=20) and tg.Hub_Ids like '%20%' select hub_name,address_id,geocode_address from #tempzeroapproved ----now the above thing gives me the output successfully--------------- ---scenario now when i try to pass the parameter 20 in stored procedure ...some problem is there...i am not getting the output --------------------------------------------------------------------------------------- ALTER PROCEDURE APPROVED_KM --20 ( @HUB_ID INT ) AS BEGIN create table #tempzeroapproved ( hub_name varchar(100), address_id int, geocode_address varchar(300) ) insert into #tempzeroapproved(hub_name,address_id,geocode_address) SELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg join tbl_user_master tum on tum.address_id=tg.address_id join tbl_facility tf on tf.facility_id=tum.facility_id WHERE tg.ADDRESS_ID NOT IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=@HUB_ID) and mmim_key is not null and tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=@HUB_ID) and tg.Hub_Ids like ''%@HUB_ID%'' UNION ALL SELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg join tbl_user_master tum on tum.address_id=tg.address_id join tbl_facility tf on tf.facility_id=tum.facility_id WHERE tg.ADDRESS_ID IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=@HUB_ID and Distance=0) and mmim_key is not null and tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=@HUB_ID) and tg.Hub_Ids like ''%@HUB_ID%'' --''%' + @LastName + '%''' select hub_name,address_id,geocode_address from #tempzeroapproved END ---how to pass this 20 --i m doing ''%@HUB_ID%'' --no result

推荐答案

- 如果我这样做就行了 ALTER PROCEDURE APPROVED_KM 20 （ @HUB_ID INT ） AS BEGIN 创建表#tempzeroapproved （ hub_name varchar（100）， address_id int， geocode_address varchar（300） ） 插入#tempzeroapproved（hub_name，address_id，geocode_address） SELECT tf.facility_name，tg.address_id，isnull（Landmark，''）+''+ isnull（Colony，''）+''+ isnull（Sub_Colony，''）+''+ isnull（CITY ，''） GEOCODE_ADDRESS FROM tbl_Geocode_Address tg 加入tbl_user_master tum on tum.address_id = tg .address_id 加入tfl_facility tf on tf.facility_id = tum.facility_id WHERE tg.ADDRESS_ID NOT IN（选择TO_ID来自tbl_approved_distances WHERE FROMID = @ HUB_ID）并且mmim_key不为空 和tum.Facility_ID in（选择来自tbl_Facility的Facility_ID，其中Hub_ID = @ HUB_ID ） 和tg.Hub_Ids喜欢'％'+ cast（@HUB_ID as varchar）+'％' UNION ALL SELECT tf。 facility_name，tg.address_id，isnull（Landmark，''）+''+ isnull（Colony，''）+''+ isnull（Sub_Colony，''）+''+ isnull（CITY，''）as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg 加入tbl_user_master tum on tum.address_id = tg.address_id 加入tbl_facility tf on tf.facility_id = tum.facility_id WHERE tg.ADDRESS_ID IN（从tbl_approved_distances选择TO_ID WHERE FROMID = @ HUB_ID和距离= 0）和mmim_key不为空 和tum.Facility_ID in（选择来自tbl_Facility的Facility_ID，其中Hub_ID = @ HUB_ID） 和tg.Hub_Ids如'％'+ cast（@HUB_ID as VARCHAR ）+'％' select hub_name，address_id，来自#tempzeroapproved的geocode_address END --it worked if i do this ALTER PROCEDURE APPROVED_KM 20 ( @HUB_ID INT ) AS BEGIN create table #tempzeroapproved ( hub_name varchar(100), address_id int, geocode_address varchar(300) ) insert into #tempzeroapproved(hub_name,address_id,geocode_address) SELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg join tbl_user_master tum on tum.address_id=tg.address_id join tbl_facility tf on tf.facility_id=tum.facility_id WHERE tg.ADDRESS_ID NOT IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=@HUB_ID) and mmim_key is not null and tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=@HUB_ID) and tg.Hub_Ids like '%' + cast(@HUB_ID as varchar) +'%' UNION ALL SELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') as GEOCODE_ADDRESS FROM tbl_Geocode_Address tg join tbl_user_master tum on tum.address_id=tg.address_id join tbl_facility tf on tf.facility_id=tum.facility_id WHERE tg.ADDRESS_ID IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=@HUB_ID and Distance=0) and mmim_key is not null and tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=@HUB_ID) and tg.Hub_Ids like '%' + cast(@HUB_ID as varchar) +'%' select hub_name,address_id,geocode_address from #tempzeroapproved END