MySQL Query Problem

Hi All,

I am stucked in query where I have to pass where condition from a user defined variable in mysql.Please provide solution for that.Please find query below

SET @v_paramcondition='CPF.Drop_Payment_Percentage>=10 and Recoupement_Order_Indicator =''N''';

PREPARE stmt FROM 'select CPF.Non_Custodial_Parent_First_Name + '' '' + CPF.Non_Custodial_Parent_Last_Name as ''ncp'',CPF.Custodial_Parent_First_Name + '' '' + CPF.Custodial_Parent_Last_Name as ''cp'',CD.IV_D_Case_Number as case_number,WD.Worker_Name as assigned_to ,MA.City_Address as office from SB_OLTP.IV_D_Case_Performance_Filter CPF join SB_OLTP.IV_D_Case_Details CD on CPF.IV_D_Case_number = CD.IV_D_Case_Number Left Join SB_OLTP.IV_D_Case_Assignment CA on ca.IV_D_Case_Number = CD.IV_D_Case_Number left join SB_REFERENCE.Worker_Detail WD on WD.Worker_Identification_Number = CA.IV_D_Case_Worker_Number Left JOIN Sb_oltp.iv_d_member_case_cross_reference MCCR on MCCR.IV_D_Case_Number=CD.IV_D_Case_Number Left JOIN SB_OLTP.IV_D_Member_Address MA on MA.Member_Number = MCCR.Member_Number Where + @v_paramcondition ';

EXECUTE stmt ;

DEALLOCATE PREPARE stmt;

Hello,

Try This:

DECLARE @Query NVARCHAR(MAX);
DECLARE @v_paramcondition NVARCHAR(MAX);
SET @v_paramcondition=' AND CPF.Drop_Payment_Percentage>=10 and Recoupement_Order_Indicator =''N''';
SET @Query = 'select CPF.Non_Custodial_Parent_First_Name + '' '' + CPF.Non_Custodial_Parent_Last_Name as ''ncp'',CPF.Custodial_Parent_First_Name + '' '' + CPF.Custodial_Parent_Last_Name as ''cp'',CD.IV_D_Case_Number as case_number,WD.Worker_Name as assigned_to ,MA.City_Address as office from SB_OLTP.IV_D_Case_Performance_Filter CPF join SB_OLTP.IV_D_Case_Details CD on CPF.IV_D_Case_number = CD.IV_D_Case_Number Left Join SB_OLTP.IV_D_Case_Assignment CA on ca.IV_D_Case_Number = CD.IV_D_Case_Number left join SB_REFERENCE.Worker_Detail WD on WD.Worker_Identification_Number = CA.IV_D_Case_Worker_Number Left JOIN Sb_oltp.iv_d_member_case_cross_reference MCCR on MCCR.IV_D_Case_Number=CD.IV_D_Case_Number Left JOIN SB_OLTP.IV_D_Member_Address MA on MA.Member_Number = MCCR.Member_Number Where 0=0';
EXECUTE (@Query+@v_paramcondition);

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Pansuriya9y ago
Hello,
Try This:
DECLARE @Query NVARCHAR(MAX);
DECLARE @v_paramcondition NVARCHAR(MAX);
SET @v_paramcondition=' AND CPF.Drop_Payment_Percentage>=10 and Recoupement_Order_Indicator =''N''';
SET @Query = 'select CPF.Non_Custodial_Parent_First_Name + '' '' + CPF.Non_Custodial_Parent_Last_Name as ''ncp'',CPF.Custodial_Parent_First_Name + '' '' + CPF.Custodial_Parent_Last_Name as ''cp'',CD.IV_D_Case_Number as case_number,WD.Worker_Name as assigned_to ,MA.City_Address as office from SB_OLTP.IV_D_Case_Performance_Filter CPF join SB_OLTP.IV_D_Case_Details CD on CPF.IV_D_Case_number = CD.IV_D_Case_Number Left Join SB_OLTP.IV_D_Case_Assignment CA on ca.IV_D_Case_Number = CD.IV_D_Case_Number left join SB_REFERENCE.Worker_Detail WD on WD.Worker_Identification_Number = CA.IV_D_Case_Worker_Number Left JOIN Sb_oltp.iv_d_member_case_cross_reference MCCR on MCCR.IV_D_Case_Number=CD.IV_D_Case_Number Left JOIN SB_OLTP.IV_D_Member_Address MA on MA.Member_Number = MCCR.Member_Number Where 0=0';
EXECUTE (@Query+@v_paramcondition);
0
vipin kumar9y ago
It's not working
0