Arrays in T-SQL?

Dave!

Hey folks,

Question for ye! I'm pretty sure this isn't possible, but I might as well double check

In a query I'm doing, I want to declare an array of values and then check if a field is in the array.

This is the current way I'm doing it:

declare @total integer

declare @country1 varchar(50) = 'IE'
declare @country2 varchar(50) = 'RU'
declare @country3 varchar(50) = 'US'
declare @country4 varchar(50) = 'KO'


select @total = COUNT(*)
from Conversion
where Conversion.ClientCountryCode in (@country1, @country2, @country3, @country4)

select ProviderCode, 
	COUNT(*) as Leads,
	case when @total > 0 then round(cast(count(*) as float) / @total * 100, 2) else 0 end as Percentage
from Conversion
where Conversion.ClientCountryCode in (@country1, @country2, @country3, @country4)
group by ProviderCode
order by Percentage desc

But what I want is something like:

declare @total integer
declare @array something = 'IE', 'RU', 'US', 'KO'

select @total = COUNT(*)
from Conversion
where Conversion.ClientCountryCode in (@array)

select ProviderCode, 
	COUNT(*) as Leads,
	case when @total > 0 then round(cast(count(*) as float) / @total * 100, 2) else 0 end as Percentage
from Conversion
where Conversion.ClientCountryCode in (@array)
group by ProviderCode
order by Percentage desc

Any suggestions for how I might achieve this?

Cheers!

fasty

I haven't really looked at what you're doing in the query itself but something like

select 'IE', 'RU', 'US', 'KO'

is as close an an array as you'll get in TSQL.

Jagera

declare a temporary table, populate it with your values, and join to that...

create table #country ( ClientCountryCode char(2) )

insert into #country values ('IE')
insert into #country values ('RU')
insert into #country values ('US')
insert into #country values ('KO')

select @total = COUNT(*)
from Conversion c
inner join #CountryCode cc on c.ClientCountryCode = cc.ClientCountryCode

select ProviderCode, 
	COUNT(*) as Leads,
	case when @total > 0 then round(cast(count(*) as float) / @total * 100, 2) else 0 end as Percentage
from Conversion c
inner join #CountryCode cc on c.ClientCountryCode = cc.ClientCountryCode
group by ProviderCode
order by Percentage desc

drop table #country -- not necessary if its in a stored procedure

whatever the datatype is of the ClientCountryCode is in the Conversion table, make the temp table the exact same (saves on converting types in the query)

Dr.Silly

Hi Dave,
Just did this, and it works.


CREATE PROCEDURE sparray
@array nvarchar(4000)

AS
BEGIN
SET NOCOUNT ON
DECLARE @nsql nvarchar(4000)
SET @nsql = '
SELECT * FROM countriesTBL
WHERE countryISO IN ( ' + @array + ') '
print @nsql
EXEC sp_executesql @nsql

END
GO

EXEC sparray
@array = '''ZW'',''US'''
GO