Array in SQL server

  • 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

  • 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!

  • 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

  • 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???

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

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