cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure varchar char convertion

Former Member
0 Kudos
5,559

I have a stored procedure not working for me

CREATE OR REPLACE PROCEDURE wslogin()
BEGIN
DECLARE @v_var varchar(256);
DECLARE @v_top int;

select value into @v_var from settings where keyword='CTYPE';
-- this value is something like E,1,3 or E,N,2,5

select count(name) into @v_top from customer where ctype in (@v_avar);
-- more code however this is the failure point I get 0 records
END

this does not find anything because customer.ctype is char(1)
I tried looping through creating varchar like 'E','1','3' ... still no luck
tried some casting and an unsuccessful array
I'm sure this may be simple, but I'm missing it.
thanks for the help

Accepted Solutions (0)

Answers (2)

Answers (2)

johnsmirnios
Advisor
Advisor
Former Member
0 Kudos

Thanks John

this worked great

select count(name) into @v_top from customer
where ctype in (select row_value from sa_split_list(@v_var));

thanks again

0 Kudos

Another option would be the LIKE Operator:
If you're sure to only have single char (or generally, matching length) elements in the list, you can check for "@v_var like '%' || ctype || '%'".
If you have elements of arbitrary length in the list, you must pre- and suffix both operands with the delimiter character, like "',' || @v_var || ',' like '%,' || ctype || ',%'".
In the second case: If you don't put the delimiters around the element you search for, you may get into matching substrings. If you don't put the extra delimiters around the list, the first and last list element won't match.

HTH
Volker
DB-TecKnowledgy