• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

redim tables in stored procedures

I'm creating a stored procedure that should have an array to be re-diminsioned
Since stored procedures does not support arrays , I used tables instead
the problem now that I need to re-diminsion that table (increase columns)
any idea how to do that?
0
Nabilbahr
Asked:
Nabilbahr
1 Solution
 
PIERCGGCommented:
Here's a code example how to add columns:

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you clarify a bit about what kind of data you need to "send/process" in the procedure?
I see 2 options: xml or delimited lists ...
let's see if this helps: http://www.experts-exchange.com/A_1536.html
0
 
NabilbahrAuthor Commented:
can i USE this code inside a function
I have created tables to act as arrays , need to re-diminsion the table by adding an extra column
I tried this code but it won't work
it is giving me

ALTER TABLE @Arr ADD c1 INT NULL
Msg 102, Level 15, State 1, Procedure GetStringing, Line 35
Incorrect syntax near


Here's a code example how to add columns:

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot use ALTER TABLE inside a function.
0
 
Anthony PerkinsCommented:
>>I'm creating a stored procedure that should have an array to be re-diminsioned<<
Incidentally and somewhat unrelated, but you cannot Redim an array to a different number of dimensions.
0
 
wdosanjosCommented:
I used the following code in the past to simulate an array on T-SQL.  This sample handles a two dimension array, but it can be easily adapted to handle any number of dimensions.  It works well for small arrays (few thousand entries), but it need some tweaks for a larger number of entries due to performance issues.

SET NOCOUNT ON

declare @array table (x int not null, y int not null, value varchar(255) null primary key (x,y))
declare @x int, @y int, @xdim int, @ydim int
declare @value varchar(255)

-- Initialize the array
set @xdim = 100
set @ydim = 75
set @x = 1
while @x <= @xdim begin
	set @y = 1
	while @y <= @ydim begin
		insert @array (x,y) values (@x, @y)
		set @y = @y + 1
	end
	set @x = @x + 1
end

select COUNT(1) as EntryCount, MAX(x) as X_UpperBound, MAX(y) as Y_UpperBound from @array

-- Set array value
update @array set value = 'Test [8,9]' where x = 8 and y = 9
update @array set value = 'Test [35,64]' where x = 35 and y = 64

-- Retrieve array value
select @value = value from @array where x = 8 and y = 9
select @value as X8_Y9
select @value = value from @array where x = 35 and y = 64
select @value as X35_Y64

-- Redim array to (130, 150)
set @xdim = 130
set @ydim = 150
set @x = 1
while @x <= @xdim begin
	select @y = ISNULL(max(y)+1,1) from @array where x = @x
	while @y <= @ydim begin
		insert @array (x,y) values (@x, @y)
		set @y = @y + 1
	end
	set @x = @x + 1
end

select COUNT(1) as EntryCount, MAX(x) as X_UpperBound, MAX(y) as Y_UpperBound from @array

Open in new window


I hope it helps.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now