i am try to execute dynamic query for that i am using CONCAT() function. but i think my string/query in long so CONCAT() is not working.
set @query:=CONCAT(' SELECT (select GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN
Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid in (', @MultiDoctorIds ,')
) as drspec,pm.id as profileid,dam.city,um.profile_url FROM Profile_Master pm LEFT JOIN DAddress_Master dam on
dam.profileid = pm.id join Unique_Url_Master um on um.clinicid =dam.id WHERE pm.id in (',@MultiDoctorIds,')
and dam.id=',@clinicId,' order by CASE WHEN um.clinic_url=''',@ClinicUrl,''' THEN 1 ELSE 2 end,um.clinic_url ');
select @query;
this is part of store procedure when i execute my SP it show me @query NULL but if i shorten my query then i get complete query.
is there another way to concatenate ??
any help appreciated.
A temporary solution for this is , store your dynamic query in local variable instead of sessional variable with maximum length .like below
Declare temp_query varchar(4000);
set @query:=CONCAT(' SELECT (select GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN
Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid in (', @MultiDoctorIds ,')
) as drspec,pm.id as profileid,dam.city,um.profile_url FROM Profile_Master pm LEFT JOIN DAddress_Master dam on
dam.profileid = pm.id join Unique_Url_Master um on um.clinicid =dam.id WHERE pm.id in (',@MultiDoctorIds,')
and dam.id=',@clinicId,' order by CASE WHEN um.clinic_url=''',@ClinicUrl,''' THEN 1 ELSE 2 end,um.clinic_url ');
select @query into temp_query;
select temp_query;