Solved

redim tables in stored procedures

Posted on 2011-02-16
6
304 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
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 142

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 142

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Set Field Values ito Zero Based on Related Table 4 28
SQL view 2 27
SQL Select to Group and Filter Data 5 15
divide by zero error 23 17
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

832 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