Professional Web Applications Themes

Stored Proc Question - MySQL

I have a stored Proc: DELIMITER $$ DROP PROCEDURE IF EXISTS `ATF`.`spSearch` $$ CREATE DEFINER=`root``%` PROCEDURE `spSearch`(IN sSearch VARCHAR(20),IN sSearchCrit varchar(45)) BEGIN SET a = sSearch; SET b = sSearchCrit; SELECT * FROM Portage_Insurance WHERE a LIKE b ORDER BY First_name ASC; END $$ DELIMITER ; and when I call it like: CALL spSearch(Last_name,'%Some Name%') I get an error saying "Unknown column 'Last_name' in 'field list'"; however I am certain that Last_name is a column in the table as well as the correct case. What am I missing?...

  1. #1

    Default Stored Proc Question

    I have a stored Proc:

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `ATF`.`spSearch` $$
    CREATE DEFINER=`root``%` PROCEDURE `spSearch`(IN sSearch
    VARCHAR(20),IN sSearchCrit varchar(45))
    BEGIN
    SET a = sSearch;
    SET b = sSearchCrit;
    SELECT * FROM Portage_Insurance WHERE a LIKE b ORDER BY First_name
    ASC;
    END $$

    DELIMITER ;

    and when I call it like:

    CALL spSearch(Last_name,'%Some Name%')

    I get an error saying "Unknown column 'Last_name' in 'field list'";
    however I am certain that Last_name is a column in the table as well
    as the correct case. What am I missing?

    HarveyCA@gmail.com Guest

  2. #2

    Default Re: Stored Proc Question

    com wrote: 
    how about this:

    call spSearch('Last_name', '%Some Name%')

    lark Guest

  3. #3

    Default Re: Stored Proc Question

     

    Here is what ended up working:

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `ATF`.`spSearch` $$
    CREATE DEFINER=`root``%` PROCEDURE `spSearch`(IN sSearch VARCHAR(20),
    IN sSearchCrit VARCHAR(45))
    BEGIN
    /*SET query = CONCAT('SELECT * FROM Portage_Insurance WHERE ',
    sSearch, ' LIKE ''%', sSearchCrit, '%''');*/
    SET query = CONCAT('SELECT * FROM Portage_Insurance WHERE ',
    sSearch, ' LIKE ''', sSearchCrit, '%''');
    PREPARE stmt FROM query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END $$

    DELIMITER ;


    HarveyCA@gmail.com Guest

  4. #4

    Default Re: Stored Proc Question

    com wrote: 
    >
    > Here is what ended up working:
    >
    > DELIMITER $$
    >
    > DROP PROCEDURE IF EXISTS `ATF`.`spSearch` $$
    > CREATE DEFINER=`root``%` PROCEDURE `spSearch`(IN sSearch VARCHAR(20),
    > IN sSearchCrit VARCHAR(45))
    > BEGIN
    > /*SET query = CONCAT('SELECT * FROM Portage_Insurance WHERE ',
    > sSearch, ' LIKE ''%', sSearchCrit, '%''');*/
    > SET query = CONCAT('SELECT * FROM Portage_Insurance WHERE ',
    > sSearch, ' LIKE ''', sSearchCrit, '%''');
    > PREPARE stmt FROM query;
    > EXECUTE stmt;
    > DEALLOCATE PREPARE stmt;
    > END $$
    >
    > DELIMITER ;
    >
    >[/ref]
    nice!
    lark Guest

Similar Threads

  1. CF to MySQL stored proc question
    By crzymnmchl in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 15th, 06:28 AM
  2. Simple Stored Proc Question
    By bzydaddy in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 2nd, 01:45 PM
  3. SQL Stored Proc question
    By Swd1974 in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 27th, 09:11 PM
  4. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  5. Simple stored proc in query question
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139