recordset looping
-
Hi
In ASP you can get a recordset and loop through it and run some code on each record.
How is this done in a stored Proc?
Thanks
-
It's preferrable that you do not. SQL is a set based language and processes sets much more efficiently. Having said that, here's an example:
DECLARE
@int_counter INT,
@int_max INT,
@txt_type CHAR(1),
@txt_object VARCHAR(256),
@txt_object_parent VARCHAR(256),
@txt_sql VARCHAR(4000)
DECLARE @objects TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
object VARCHAR(256),
object_parent VARCHAR(256),
object_type CHAR(1))
INSERT @objects(
object,
object_parent,
object_type)
SELECT CONSTRAINT_NAME, TABLE_NAME, 'C'
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
UNION ALL
SELECT name, '','U'
FROM sysobjects
WHERE
xtype = 'U'
AND name NOT LIKE 'dt%'
SELECT
@int_counter = 1,
@int_max = (SELECT MAX(ident) FROM @objects)
WHILE @int_counter <= @int_max
BEGIN
SELECT
@txt_type = o.object_type,
@txt_object = o.object,
@txt_object_parent = o.object_parent
FROM
@objects o
WHERE
o.ident = @int_counter
IF @txt_type = 'C'
BEGIN
SELECT @txt_sql = 'ALTER TABLE ' + @txt_object_parent + ' DROP CONSTRAINT ' + @txt_object
END
ELSE
BEGIN
SELECT @txt_sql = 'DROP TABLE ' + @txt_object
END
PRINT @txt_sql
SELECT @int_counter = @int_counter + 1
END
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Thanks.
Not very elegant, I'll take your advice and do the looping in the ASP page.
Regards
Andy.
-
I agree that looping through the recordset within SQL is something to be avoided if possible. However, when I find a TRUE need for it, I use the dreaded CURSOR to do it. You can find more information about using Cursors in SQL BOL. One example from BOL is:
USE pubs
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE "B%"
ORDER BY au_lname, au_fname
OPEN authors_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT "Author: " + @au_fname + " " + @au_lname
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply