Solved

redim tables in stored procedures

Posted on 2011-02-16
6
308 Views
Last Modified: 2012-05-11
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
Comment
Question by:Nabilbahr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 6

Expert Comment

by:PIERCGG
ID: 34909989
Here's a code example how to add columns:

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34910067
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
 

Author Comment

by:Nabilbahr
ID: 34916249
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34916439
you cannot use ALTER TABLE inside a function.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34916511
>>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
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 34920329
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question