Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

redim tables in stored procedures

Posted on 2011-02-16
6
Medium Priority
?
310 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

610 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