Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Array in SQL server Expand / Collapse
Author
Message
Posted Thursday, September 2, 2010 1:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
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
Post #979326
Posted Thursday, September 2, 2010 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 12,901, Visits: 32,138
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #979444
Posted Thursday, September 2, 2010 5:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 5,359, Visits: 8,921
I would suggest you read this article Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #979447
Posted Thursday, September 2, 2010 9:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
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???
Post #979658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse