Openquery with array

skywalker · May 21st, 2010, 12:52 PM

Hi Im having some problems with passing a list to an openquery call. Evey thing process correctly until the group_no in call. I am setting this list with the following code:

Code:

declare @array char (1000)
declare @temp_array char (500)
set @temp_array = '1053,1112,1149,1210,1227,1233,1255,1258,1228'
select @temp_array =  replace(@temp_array,',',''''',''''')
set @array =  (''''+'!'+'''')
select @array = replace (@array,'!',@temp_array)

This works fine for 3 or less items in the list, however it bombs out for any thing greater than that

Entire Call:

Code:

select @sql = 'insert into #temp_refund_export (cert_no, member_no, plan_no, ben_year, cov_code, group_no, act_code, type)
SELECT cert_no, member_no, plan_no, ben_year, cov_code, group_no, act_code, ''cheque'' as type  
FROM OPENQUERY(Saslink,''SELECT cert_no, member_no, plan_no, ben_year, cov_code, group_no, act_code from g_certif
		  where ((plan_no = ' + '''''' + rtrim(convert varchar,@ic_TERM1)) + '''''' + ' and ben_year = ' +
			         '''''' + rtrim(convert(varchar,@ic_benefit_year1)) + '''''' + 
			 ') or 
		         (plan_no = ' + '''''' + rtrim(convert(varchar,@ic_TERM2)) + '''''' +
		          ' and ben_year = ' + '''''' + rtrim(convert(varchar,@ic_benefit_year2)) + '''''' + 
		        ')
		        )
		  and act_code = ' + '''''' + cast (@ic_act_code as varchar) + '''''' + '
		  and cov_code <> ' + '''''' + cast ('1' as varchar) + '''''' + '
		  and group_no in (' + '''' + rtrim(convert(varchar,@array)) + '''' + ')'+	    ''')'
 
EXEC(@sql)

The error I get it:

Server: Msg 105, Level 15, State 1, Line 12
Unclosed quotation mark before the character string ')'.

Any pointers in the right direction would be very appreciated

Skywalker

Old Pedant · May 21st, 2010, 08:28 PM

Can I ask a dumb question?

What is the data type of your group_no field???

Because if it is INT (or any numeric type), then you are going all wrong putting the '...' around each item, in the first place.

And if it is not INT, could you use it as INT?

In other words:

Code:

set @array = '1053,1112,1149,1210,1227,1233,1255,1258,1228'
...
select @sql = '.....
        and CONVERT(INT,group_no) in (' + @array + ')'+
       ...

???

If the values in @array are always integers, why work harder than that?

skywalker · May 21st, 2010, 08:38 PM

No unfortunately its a char, otherwise it would have been a lot easier.

Old Pedant · May 21st, 2010, 08:40 PM

Code:

declare @array char (1000)
declare @temp_array char (500)
set @temp_array = '1053,1112,1149,1210'
select @temp_array =  replace(@temp_array,',',''''',''''')
--  Okay, so at this point @temp_array should contain
-- 1053'',''1112'',''1149'',''1210
set @array =  (''''+'!'+'''')
-- so @array contains
--   '!'
select @array = replace (@array,'!',@temp_array)
-- and now @array contains
-- '1053'',''1112'',''1149'',''1210'
 
    and group_no in (' + '''' + rtrim(convert(varchar,@array)) + '''' + ')'+  
-- should result in
    and group_no in (''1053'',''1112'',''1149'',''1210'')'+

Which does look right.

Kind of a convoluted way to get there, but whatever.

I think I would have done this:

Code:

declare @array char (1000)
declare @temp_array char (500)
set @temp_array = '1053,1112,1149,1210'
select @array =  '$' + replace(@temp_array,',','$,$') + '$'
...
    and group_no in (' + replace(@array,'$','''''') + ')'+

Assuming you can't simply do the CONVERT(INT,group_no) trick.

Old Pedant · May 21st, 2010, 08:45 PM

Okay, so group_no is char. But are any of the values in the IN( ) list ever non-numeric??

Because if they aren't, you could do:

Code:

    and ( CASE WHEN ISNUMERIC(group_no) = 1 AND CONVERT(INT,group_no) IN (' + @array + ') THEN 1
           ELSE 0 END ) = 1 ' +

skywalker · May 21st, 2010, 10:23 PM

Thanks for the two post's, you are right in assuming that the char field will only every contain numeric's. Ill try out the two methods that you suggest, although why would the openquery accept 3 elements in the list but not 4.

I realise that the solution I setup is bit long winded, Im more at ease with Perl in which you could do a very simple 1 line regular expression.

Thanks

skywalker

skywalker · May 25th, 2010, 10:42 AM

Thanks Old Pedant, the replace worked perfectly

Code:

select @array =  '$' + replace(@temp_array,',','$,$') + '$'
...
    and group_no in (' + replace(@array,'$','''''') + ')'+