Hi,

    I am writing a stored procedure and I was wondering if it is possible to
declare an integer array as a local variable.   I want to do something like
this:

DECLARE     iRevenueTier(6)    Integer

   I have tried it in Query Analyzer and it gave me an error, but possibly I
am just using incorrect syntax.

   Any help appreciated.

JD

Although there are no array types in SQL Server, there are various
alternatives depending on what you are trying to accomplish.  Below is an
example using a table variable.

DECLARE @RevenueTiers TABLE
(
    Ordinal int NOT NULL PRIMARY KEY,
    iRevenueTier int NOT NULL
)

INSERT INTO @RevenueTiers VALUES(1, 100)
INSERT INTO @RevenueTiers VALUES(2, 200)
INSERT INTO @RevenueTiers VALUES(3, 300)
INSERT INTO @RevenueTiers VALUES(4, 400)
INSERT INTO @RevenueTiers VALUES(5, 500)
INSERT INTO @RevenueTiers VALUES(6, 600)

SELECT iRevenueTier
FROM @RevenueTiers
WHERE Ordinal = 1

news:BGgZe.266045$E95.143426@fed1read01...
Show quoteHide quote

No, you are using the wrong mind set.    SQL has one and only one data
structure, the table.  It is also a strongly typed language, so we
follow the ISO-11179 rules and do not put those silly "i-" prefixes on
data element names.

You might use six local integers named revenue_tier_1, revenue_tier_2,
..., revenue_tier_6.

But you should not be writting code that uses loops because SQL is also
a declarative language.  Post your code and we can probably improve it.

Joe,

In addition to the possibility of using a table, as Dan suggested, there
is the option of using a character string or binary string to hold your
data.  More often than not, a table (which is effectively an associative
array) is the best solution, but sometimes a single-string implementation
of an array works out.  An array may be more efficient, at the expense
of the code being harder to maintain.

declare @A6 binary(24)
set @A6 = 0x  -- Initialize @A6 to all zeros, and non-null

-- A6[1] = 12345
set @A6 = cast(12345 as binary(4)) + substring(@A6,5,20)

-- A6[4] = -789456
set @A6 =
  substring(@A6,1,12) +
  cast(-789456 as binary(4)) +
  substring(@A6,17,8)

select
  i, cast(substring(@A6,4*i-3,4) as int) as [A[i]]]
from (
  select 1 as i union all select 2 union all select 3
  union all select 4 union all select 5
) N


Steve Kass
Drew University


Joe Delphi wrote:

Show quoteHide quote

Triggers - run for multiple updated
parsing varchar fields
INSTEAD OF INSERT trigger
Why does update run differently standalone vs in procedure?
SELECT different columns based on the value of another column