Блог

удалить функцию, если она существует rty_check_member_info_status;

РАЗДЕЛИТЕЛЬ $$

—

— Функции

CREATE DEFINER= root @ localhost FUNCTION rty_check_member_info_status (memb_id int,field_name_1 varchar(100),field_name_2 varchar(100),login_member_amount int(11),login_status char(1)) ВОЗВРАЩАЕТ символ (1) КОДИРОВКА latin1 begin объявить fn_field_name_1 varchar(100) ; объявить fn_field_name_2 varchar(100) ; объявить fn_amount_for_profile_visible int(11);

 declare fn_return char(1) default 'N';

declare test_field varchar(100);    

select field_name_1,field_name_2,amount_for_profile_visible into
fn_field_name_1,fn_field_name_2,fn_amount_for_profile_visible
from member_account_settings inner join tbl_members on member_account_settings.member_auto_id = tbl_members.member_id 
where tbl_members.member_id = memb_id  ;

if fn_field_name_1 = 'H' Then
   set fn_return = 'N' ;
else
if fn_field_name_2 = 'Y' Then
    if fn_amount_for_profile_visible = '0' Then
    set fn_return = 'Y' ;
    else
       if login_status = 1 Then
              if fn_amount_for_profile_visible > login_member_amount Then
              set fn_return = 'N' ;
              else
              set fn_return = 'Y' ; 
              end if;
       else 
       set fn_return = 'N';  
       end if ;  
    end if;    
else
set fn_return = 'Y';
end if ;
end if ;

return fn_return ;
  

end $$
РАЗДЕЛИТЕЛЬ ;

У вас есть два варианта: в значительной степени сгенерированный SQL (обычно плохая идея, потому что его сложнее писать, отлаживать и документировать) и использование оператора case для выбора столбца на основе имени, соответствующего строке (что обычно является довольно хорошим решением).

Вот пример второго, поскольку это решение, которое я бы определенно рекомендовал.

 SET @test_field1 = "last_name_display_status" ;
SET @test_field2 = "last_name_display_for_other_partcpnt" ;

SELECT
     CASE @test_field1
        -- List columns here that you might want to return:
        WHEN 'last_name_display_status' THEN last_name_display_status
        WHEN 'last_name_display_for_other_partcpnt' THEN last_name_display_for_other_partcpnt
        WHEN 'create_date' THEN create_date
        -- Return a value for an invalid name here:
        ELSE NULL
     END AS test_field1,
     CASE @test_field2
        -- List columns here that you might want to return:
        WHEN 'last_name_display_status' THEN last_name_display_status
        WHEN 'last_name_display_for_other_partcpnt' THEN last_name_display_for_other_partcpnt
        WHEN 'create_date' THEN create_date
        -- Return a value for an invalid name here:
        ELSE NULL
     END AS test_field2,
     -- Rest of select unaffected by this change
     amount_for_profile_visible
   INTO
     fn_field_name_1,
     fn_field_name_2,
     fn_amount_for_profile_visible
FROM member_account_settings
INNER JOIN tbl_members
  ON member_account_settings.member_auto_id = tbl_members.member_id
WHERE
  tbl_members.member_id = memb_id
;
  

И для полноты картины копия первого решения, которое я предложил (сгенерированный SQL):

 -- Need to use @vars, since named vars aren't in scope for the generated SQL:
SET @output1 = '';
SET @output2 = '';
SET @output3 = '';
SET @input1 = memb_id;

-- We also need to store our generated SQL to a variable
SET @query = 'SELECT '   @test_field1   ','   @test_field2   ', amount_for_profile_visible INTO @output1, @output2, @output3 FROM member_account_settings INNER JOIN tbl_members ON member_account_settings.member_auto_id = tbl_members.member_id WHERE tbl_members.member_id = ?';
-- To execute the code we have to convert it to a prepared statement
-- named stmt here, because it's what most people use in this instance
PREPARE stmt FROM @query;
-- Execute the statement using our input variable
EXECUTE stmt USING @input1;
-- Delete the prepared statement now we've run it.
DEALLOCATE PREPARE stmt;

-- Store our @vars back into the named vars.
SET fn_field_name_1 = @output1;
SET fn_field_name_2 = @output2;
SET fn_amount_for_profile_visible = @output3;