T-SQL ASP While not rs.EOF

[brentster]

I am trying to write a stored procedure that utilizes something like a
while in ASP.

my select statement is like this.

s = select distinct(employ ee_id) from employee_table
rs.Open s

while not rs.EOF

then I want to use employee_id in about twenty different sql
statements. They are all different and they utilize multiple joins. I
use four - six different tables to find out a count in the sql
statement. Then I want to use the value from each of the sql
statements at the end to use in an insert statement. What would be the
best way.

insert into table (employee_id, value, value, etc..)

if not rs.EOF then
rs.MoveNext
end if
wend

Any help would be appreciated.

[Trevor Best]

Re: T-SQL ASP While not rs.EOF

brentster wrote:[color=blue]
> I am trying to write a stored procedure that utilizes something like a
> while in ASP.
>
> my select statement is like this.
>
> s = select distinct(employ ee_id) from employee_table
> rs.Open s
>
> while not rs.EOF
>
> then I want to use employee_id in about twenty different sql
> statements. They are all different and they utilize multiple joins. I
> use four - six different tables to find out a count in the sql
> statement. Then I want to use the value from each of the sql
> statements at the end to use in an insert statement. What would be the
> best way.
>
> insert into table (employee_id, value, value, etc..)
>
> if not rs.EOF then
> rs.MoveNext
> end if
> wend
>
> Any help would be appreciated.[/color]
Query Analyser has templates for using cursors, e.g.

--- Code Start ---
-- =============== =============== ===============
-- Declare and using an UPDATE cursor
-- =============== =============== ===============
DECLARE <@variable_1, sysname, @v1> <datatype_for_v ariable_1, sysname,
varchar(20)>,
<@variable_2, sysname, @v2> <datatype_for_v ariable_2, sysname, varchar(40)>

DECLARE <cursor_name, sysname, test_cursor> CURSOR
FOR SELECT <column_1, sysname, au_fname>, <column_2, sysname, au_lname>
FROM <table_name, sysname, pubs.dbo.author s>
FOR UPDATE of <column_1, sysname, au_fname>

DECLARE @count smallint
SELECT @count = 1

OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO <@variable_1,
sysname, @v1>, <@variable_2, sysname, @v2>

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user-defined code here...'
-- eg
PRINT 'updating record of ' + @v1 + ' ' + @v2
UPDATE pubs.dbo.author s
SET au_fname = @v1 + '-' + CAST(@count AS varchar(4))
WHERE au_lname = @v2
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO <@variable_1,
sysname, @v1>, <@variable_2, sysname, @v2>
SELECT @count = @count + 1
END

CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
GO
--- Code Ends ---

But consider trying to accomplish what you want to do using a batch SQL
statement as that'll be faster.

--
But why is the Rum gone?

[John Bell]

Re: T-SQL ASP While not rs.EOF

Hi

A set based solution should be far more efficient that your cursor based
solution especially with larger amounts of data.

There is a large amount of documentation in Books online for cursors an
example:
mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ts q
lref.chm::/ts_de-dz_31yq.htm

John


"brentster" <brentchristoph er@hotmail.com> wrote in message
news:d9e99753.0 404170643.7b0db e61@posting.goo gle.com...[color=blue]
> I am trying to write a stored procedure that utilizes something like a
> while in ASP.
>
> my select statement is like this.
>
> s = select distinct(employ ee_id) from employee_table
> rs.Open s
>
> while not rs.EOF
>
> then I want to use employee_id in about twenty different sql
> statements. They are all different and they utilize multiple joins. I
> use four - six different tables to find out a count in the sql
> statement. Then I want to use the value from each of the sql
> statements at the end to use in an insert statement. What would be the
> best way.
>
> insert into table (employee_id, value, value, etc..)
>
> if not rs.EOF then
> rs.MoveNext
> end if
> wend
>
> Any help would be appreciated.[/color]