T_SQL, lists and arrays

[john ettinger]

I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...

Loop from 0 to ListLength
begin
INSERT Transaction_Data
end

Thanks from an SQLS7 newbie,

jE

[Adam Fortney]

John,

I believe passing an array to the sp_ is not an option.

Here are a couple of suggestions:

1. If the list will always contain x number of items, you could declare in the sp_ the x number of parameters that you will be passing in, copy those values to a temp table, then use a CURSOR in the sp_ to go through the temp table's data.

2. If the list will contain varying number of items...Create a temp table(from the calling code outside of the sp_), copy the list information to it. Then use a CURSOR in the sp_ to go through the temp table's data.

Here is the basic syntax for a CURSOR:

DECLARE @Column1,@Column2 AS INT

DECLARE CursorName CURSOR FOR
SELECT Column1,Column2..
FROM #TempTable
ORDER BY Column1
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
WHILE @@FETCH_STATUS = 0
BEGIN
[Code goes here]
INSERT...
END
CLOSE CursorName
DEALLOCATE CursorName


I hope this helps.
Adam Fortney

------------
john ettinger at 12/30/99 7:48:03 PM


I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...

Loop from 0 to ListLength
begin
INSERT Transaction_Data
end

Thanks from an SQLS7 newbie,

jE

[Adam Fortney]

John,

In my CURSOR example I ommitted FETCH NEXT FROM CursorName INTO @Column1,@Column2....within the WHILE loop. Also it's a good idea to drop the temp table.

Here it is:
DECLARE @Column1,@Column2 AS INT

DECLARE CursorName CURSOR FOR
SELECT Column1,Column2..
FROM #TempTable
ORDER BY Column1
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
END
CLOSE CursorName
DEALLOCATE CursorName
DROP TABLE #TempTable

------------
Adam Fortney at 1/4/00 4:27:29 PM

John,

I believe passing an array to the sp_ is not an option.

Here are a couple of suggestions:

1. If the list will always contain x number of items, you could declare in the sp_ the x number of parameters that you will be passing in, copy those values to a temp table, then use a CURSOR in the sp_ to go through the temp table's data.

2. If the list will contain varying number of items...Create a temp table(from the calling code outside of the sp_), copy the list information to it. Then use a CURSOR in the sp_ to go through the temp table's data.

Here is the basic syntax for a CURSOR:

DECLARE @Column1,@Column2 AS INT

DECLARE CursorName CURSOR FOR
SELECT Column1,Column2..
FROM #TempTable
ORDER BY Column1
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
WHILE @@FETCH_STATUS = 0
BEGIN
[Code goes here]
INSERT...
END
CLOSE CursorName
DEALLOCATE CursorName


I hope this helps.
Adam Fortney

------------
john ettinger at 12/30/99 7:48:03 PM


I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...

Loop from 0 to ListLength
begin
INSERT Transaction_Data
end

Thanks from an SQLS7 newbie,

jE

There are no arrays in sql, only temporary tables.

However you can pass a delimted string as a parameter and just parse it within a loop. This gets a bit risky considering the things that could get passed so you need to code very precisely how you will handle the input parameter. Important to would be speed and size. This isn't a valid option if the parameter list is going to take in 1000+ variables or if the procedure needs to execute quickly (In that case cursors and temp tables are not an option either).

Also when doing this you need to pick an appropriate delimeter or fixed size field.

If all you want to do is row by row processing of a table before you insert records, use a cursor. This will allow you to *fetch* one row at a time, do any processing and then will repeat until the end of the query or table has been reached.

Cursor syntax is simple:

Declare @Fname varchar(20)

Declare myCursor Cursor For
Select FName From Employees
Where Fname like 'A%'

Open myCursor
Fetch Next From MyCursor Into @Fname

do while @@Fetch_Status = 0
begin
Print @Fname -- add your own processing here
Fetch Next From MyCursor Into @Fname
end

CLOSE MyCursor
DEALLOCATE MyCursor

-- Do make sure you clean up after you use a cursor. If you don't they will stay in memory until someone decides to kill them.


------------
Adam Fortney at 1/4/00 4:36:54 PM

John,

In my CURSOR example I ommitted FETCH NEXT FROM CursorName INTO @Column1,@Column2....within the WHILE loop. Also it's a good idea to drop the temp table.

Here it is:
DECLARE @Column1,@Column2 AS INT

DECLARE CursorName CURSOR FOR
SELECT Column1,Column2..
FROM #TempTable
ORDER BY Column1
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
END
CLOSE CursorName
DEALLOCATE CursorName
DROP TABLE #TempTable

------------
Adam Fortney at 1/4/00 4:27:29 PM

John,

I believe passing an array to the sp_ is not an option.

Here are a couple of suggestions:

1. If the list will always contain x number of items, you could declare in the sp_ the x number of parameters that you will be passing in, copy those values to a temp table, then use a CURSOR in the sp_ to go through the temp table's data.

2. If the list will contain varying number of items...Create a temp table(from the calling code outside of the sp_), copy the list information to it. Then use a CURSOR in the sp_ to go through the temp table's data.

Here is the basic syntax for a CURSOR:

DECLARE @Column1,@Column2 AS INT

DECLARE CursorName CURSOR FOR
SELECT Column1,Column2..
FROM #TempTable
ORDER BY Column1
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
WHILE @@FETCH_STATUS = 0
BEGIN
[Code goes here]
INSERT...
END
CLOSE CursorName
DEALLOCATE CursorName


I hope this helps.
Adam Fortney

------------
john ettinger at 12/30/99 7:48:03 PM


I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...

Loop from 0 to ListLength
begin
INSERT Transaction_Data
end

Thanks from an SQLS7 newbie,

jE

[john ettinger]

Thanks! This helped a lot!

Cheers,

jE


------------
at 1/4/00 5:52:16 PM


There are no arrays in sql, only temporary tables.

However you can pass a delimted string as a parameter and just parse it within a loop. This gets a bit risky considering the things that could get passed so you need to code very precisely how you will handle the input parameter. Important to would be speed and size. This isn't a valid option if the parameter list is going to take in 1000+ variables or if the procedure needs to execute quickly (In that case cursors and temp tables are not an option either).

Also when doing this you need to pick an appropriate delimeter or fixed size field.

If all you want to do is row by row processing of a table before you insert records, use a cursor. This will allow you to *fetch* one row at a time, do any processing and then will repeat until the end of the query or table has been reached.

Cursor syntax is simple:

Declare @Fname varchar(20)

Declare myCursor Cursor For
Select FName From Employees
Where Fname like 'A%'

Open myCursor
Fetch Next From MyCursor Into @Fname

do while @@Fetch_Status = 0
begin
Print @Fname -- add your own processing here
Fetch Next From MyCursor Into @Fname
end

CLOSE MyCursor
DEALLOCATE MyCursor

-- Do make sure you clean up after you use a cursor. If you don't they will stay in memory until someone decides to kill them.


------------
Adam Fortney at 1/4/00 4:36:54 PM

John,

In my CURSOR example I ommitted FETCH NEXT FROM CursorName INTO @Column1,@Column2....within the WHILE loop. Also it's a good idea to drop the temp table.

Here it is:
DECLARE @Column1,@Column2 AS INT

DECLARE CursorName CURSOR FOR
SELECT Column1,Column2..
FROM #TempTable
ORDER BY Column1
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
END
CLOSE CursorName
DEALLOCATE CursorName
DROP TABLE #TempTable

------------
Adam Fortney at 1/4/00 4:27:29 PM

John,

I believe passing an array to the sp_ is not an option.

Here are a couple of suggestions:

1. If the list will always contain x number of items, you could declare in the sp_ the x number of parameters that you will be passing in, copy those values to a temp table, then use a CURSOR in the sp_ to go through the temp table's data.

2. If the list will contain varying number of items...Create a temp table(from the calling code outside of the sp_), copy the list information to it. Then use a CURSOR in the sp_ to go through the temp table's data.

Here is the basic syntax for a CURSOR:

DECLARE @Column1,@Column2 AS INT

DECLARE CursorName CURSOR FOR
SELECT Column1,Column2..
FROM #TempTable
ORDER BY Column1
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @Column1,@Column2
[Code goes here]
INSERT...
WHILE @@FETCH_STATUS = 0
BEGIN
[Code goes here]
INSERT...
END
CLOSE CursorName
DEALLOCATE CursorName


I hope this helps.
Adam Fortney

------------
john ettinger at 12/30/99 7:48:03 PM


I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...

Loop from 0 to ListLength
begin
INSERT Transaction_Data
end

Thanks from an SQLS7 newbie,

jE