[Resolved] How to declare array on Stored Proc.

[naruponk]

Hi, there

Does any one know that how to declare an array on Stored Procedure.

Dim Arr() As int
Redim Arr(5)

How can i convert above code to Stored Procedure?

Thanks

[kaffenils]

SQL Server doesn't have arrays, but there are ways to simulate arrays. One way is to use UDF (user defined functions) and a varchar variable the holds a comma/semicolon separated list of your array elements. Create one UDF that adds/updates an element into the variable and another UDF to return an element from a position in the variable.

You can also take a look at this page to get more ideas http://www.sommarskog.se/arrays-in-sql.html

[szlamany]

Hi, there

Does any one know that how to declare an array on Stored Procedure.

Dim Arr() As int
Redim Arr(5)

How can i convert above code to Stored Procedure?

Thanks

Declare @Arr Table (Slot int, Val int)

Insert Into @Arr (1,123)
Insert Into @Arr (2,456)
Insert Into @Arr (3,789)
Insert Into @Arr (4,112233)
Insert Into @Arr (5,445566)

In a way that TABLE VARIABLE becomes an array - the SLOT column mimics the ARRAY size and the VAL column is your INTEGER value.

You could use it as:

Set @SOMEVARIABLE=(SELECT VAL FROM @ARR WHERE SLOT=3)

That little sub-query shown to the right of the = SIGN could be used pretty much anywhere - INSERT, UPDATE, WHERE clauses.

The UDF method described by the prior post will work as well.

But the bigger question is why? For the most part arrays are for iterative/logic driven languages - T-SQL is supposed to be "set-based" - and letting go of those "language" constructs can be challenging - but very well worth it.

[naruponk]

Thanks kaffenils,szlamany