Array in SQL server

Question

Array in SQL server

BeginnerBug

Hall of Fame

Points: 3539
More actions
September 2, 2010 at 1:22 am

#73692

While returning the result set from the sqlserver 2k5 to java, can i set a bulk of data in a single output variable?..
for example a SP returns select @ename=empname from employee_details... where empname has 10,2,30,40... and @ename is an output variable...
Pls Reply ASAP..if u come across of this sort of issue

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

SQL Server thinks of arrays as tables, so it doesn't have an array data type....

to decode a string from a delimited list to a table, you can use one of the many Split functions contributed in the scrips section.

to convert rows of data back into a delimited list, you need to use a trick with FOR XML.

create function [dbo].[fn_split](

@STR varchar(8000),

@spliter char(1)

)

returns @returnTable table (idx int primary key identity, item varchar(8000))

as

begin

declare @spliterIndex int

select @STR = @STR + @spliter

SELECT @STR = @spliter + @STR + @spliter

INSERT @returnTable

SELECT SUBSTRING(@str,N+1,CHARINDEX(@spliter,@str,N+1)-N-1)

FROM dbo.Tally

WHERE N < LEN(@str)

AND SUBSTRING(@str,N,1) = @spliter

ORDER BY N

return

end

declare @skills table (Resource_Id int, Skill_Id varchar(20))

insert into @skills

select 101, 'sqlserver' union all

select 101, 'vb.net' union all

select 101, 'oracle' union all

select 102, 'sqlserver' union all

select 102, 'java' union all

select 102, 'excel' union all

select 103, 'vb.net' union all

select 103, 'java' union all

select 103, 'oracle'

---

select * from @skills s1

--- Concatenated Format

set statistics time on;

SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id

FROM @skills s2

WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below

ORDER BY Skill_Id

FOR XML PATH('')

),1,1,'') as [Skills]

FROM @skills s1

GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned

ORDER BY s1.Resource_Id

set statistics time off;

--- CrossTab Format

SELECT Resource_Id

,MAX(case when skill_id = 'Excel' then 'Yes' else '' end) as Excel

,MAX(case when skill_id = 'Java' then 'Yes' else '' end) as Java

,MAX(case when skill_id = 'Oracle' then 'Yes' else '' end) as Oracle

,MAX(case when skill_id = 'SQLServer' then 'Yes' else '' end) as SQLServer

,MAX(case when skill_id = 'VB.Net' then 'Yes' else '' end) as [VB.Net]

FROM @skills

Group by Resource_Id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 2

I would suggest you read this article Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 3

Hi..

Thanx a lot for ur effort.... but my doubt is how can i pass a result set to java....

for ex.. i have empno from employee table.

10

20

30

select @empno=empno from employee

if @empno is a out parameter.. can it hold 10,20,30 values???