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

本文介绍了在存储过程中的like运算符中传递变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 创建表#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,'') asGEOCODE_ADDRESSFROMtbl_Geocode_Address tgjoin tbl_user_master tum on tum.address_id=tg.address_idjoin tbl_facility tf on tf.facility_id=tum.facility_idWHEREtg.ADDRESS_ID NOT IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=20) and mmim_key is not nulland tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=20)and tg.Hub_Ids like '%20%'UNION ALLSELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') asGEOCODE_ADDRESSFROMtbl_Geocode_Address tgjoin tbl_user_master tum on tum.address_id=tg.address_idjoin tbl_facility tf on tf.facility_id=tum.facility_idWHEREtg.ADDRESS_ID IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=20 and Distance=0) and mmim_key is not nulland 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------------------scenarionow 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)ASBEGINcreate 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,'') asGEOCODE_ADDRESSFROMtbl_Geocode_Address tgjoin tbl_user_master tum on tum.address_id=tg.address_idjoin tbl_facility tf on tf.facility_id=tum.facility_idWHEREtg.ADDRESS_ID NOT IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=@HUB_ID) and mmim_key is not nulland tum.Facility_ID in(select Facility_ID from tbl_Facility where Hub_ID=@HUB_ID)and tg.Hub_Ids like ''%@HUB_ID%''UNION ALLSELECT tf.facility_name,tg.address_id,isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'') asGEOCODE_ADDRESSFROMtbl_Geocode_Address tgjoin tbl_user_master tum on tum.address_id=tg.address_idjoin tbl_facility tf on tf.facility_id=tum.facility_idWHEREtg.ADDRESS_ID IN(SELECT TO_ID FROM tbl_approved_distances WHERE FROMID=@HUB_ID and Distance=0) and mmim_key is not nulland 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 #tempzeroapprovedEND---how to pass this 20--i m doing ''%@HUB_ID%''--no result推荐答案 这篇关于在存储过程中的like运算符中传递变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！