?
Solved

redim tables in stored procedures

Posted on 2011-02-16
6
Medium Priority
?
309 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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 ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 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