Creating array in sql server

Question

Creating array in sql server

rama.king127

SSC Rookie

Points: 37
More actions
April 17, 2013 at 4:05 am

#292288

A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it

Viewing 6 posts - 1 through 5 (of 5 total)

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

haiao2000 Hall of Fame Points: 3734 More actions · Answer 1

rama.king127 (4/17/2013)
A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it

Think outside the box, the sql way like this...

set nocount on;

declare @sqlArray table (id int identity(1,1), item int)

insert into @sqlArray(item)

select 3 union all

select 4;

declare @x int=1, @arrayLength int, @curItem int

select @arrayLength=MAX(id) from @sqlArray;

--now loop thru your array =)

while @x <= @arrayLength begin

select @curItem = item from @sqlArray where id=@x;

print @curItem;

set @x= @x+1;

end

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

You may want to have a read about custom types.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

haiao2000 (4/17/2013)
rama.king127 (4/17/2013)
A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it
Think outside the box, the sql way like this...
set nocount on;
declare @sqlArray table (id int identity(1,1), item int)
insert into @sqlArray(item)
select 3 union all
select 4;
declare @x int=1, @arrayLength int, @curItem int
select @arrayLength=MAX(id) from @sqlArray;
--now loop thru your array =)
while @x <= @arrayLength begin
select @curItem = item from @sqlArray where id=@x;
print @curItem;
set @x= @x+1;
end

Gosh, no. Think outside the box. 😉 Don't use RBAR for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

rama.king127 (4/17/2013)
A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it

Will you always be passing just 2 parameters like this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mansfield SSCrazy Points: 2396 More actions · Answer 5

Also consider Table Value Parameters or some other kind of list shredding method.