1. 

   jbonfante, Member

   I've setup a report parameter, in this case varchar(255), where I want to input a list of zipcodes "90210, 60653...etc", then use the parameter as part of a where in clause. This should return only records that match the list of zipcodes. However everything I've tried so far does not appear to be working. Below the convert statement only takes into consideration the first number in the list, and disregards everything else. Is there another way of doing this? Thanks for your help.

   CREATE PROCEDURE sp_DBR_LeadReport_By_Zip(zipcode varchar(255))
   BEGIN
   set @vSQL = convert(zipcode,unsigned);

   select l.first_name as 'First Name',
   l.last_name as 'Last Name',
   l.dob as 'Dob',
   l.phone as 'Phone'
   from Lead_Management_lead l
   where l.zipcode in ( @vSQL)
   ;
2. 

   myDBR Team, Key Master

   Hi,
   You can use dynamic SQL to get the zip codes. In your example the convert function just gets the first value.

   
   
   drop temporary table if exists zip_tmp;
   
   create temporary table zip_tmp ( zip int );
   
   set @vSQL = concat('insert into zip_tmp select l.zipcode from Lead_Management_lead l where l.zipcode in (', zipcode, ')');
   
   prepare stmt from @vSQL;
   
   execute stmt;
   
   deallocate prepare stmt;
   
   

   --
   myDBR Team

Reply

You must log in to post.