I have a stored procedure not working for me

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

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

asked 05 Dec '16, 13:27

tandersen42's gravatar image

accept rate: 0%

permanent link

answered 05 Dec '16, 13:35

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

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

(05 Dec '16, 15:15) tandersen42

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.


permanent link

answered 06 Dec '16, 03:47

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
accept rate: 25%

edited 06 Dec '16, 09:29

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 05 Dec '16, 13:27

question was seen: 5,225 times

last updated: 06 Dec '16, 09:29