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