在存储过程中的like运算符中传递变量 [英] pass variable in like operator in stored procedure

问题描述

创建表#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

这篇关于在存储过程中的like运算符中传递变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！