traversing a table without cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

traversing a table without cursor

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 06:44:02

If i have a table with 3 fields (namely field1,field2, field3) and I need to check all 3 fields to execute a query, and need to repeat this procedure for all records in the table, what's the best approach?

I have considered using cursors but I hope someone has a better idea out there.

thanks in advance...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-09-06 : 07:27:22

Can you give some sample data and an example of the desired output?

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-06 : 07:31:59

Can your query be part of a UDF?

If so, you could just do select yourfunction(col, col, col) from tablename

-------
Moo. :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-06 : 07:36:39

u could use :

exec master..xp_execresultset 'select ... from...', database

but if this is for production then rather don't.

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 22:59:18

the process is part of a store procedure.

here's the sample result set. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39478

Data will be coming from the result of the joins, i've added the sysobjects.xtype field to determine what is the object type

Each row will be processed according to the the 3 fields:
if the object type is a procedure then I issue a revoke execute on the objectname field from particular user

I'm the lone DBA and my objective is to automate most of the processes that are routine in nature and does not require my special attention.

Thanks in advance...

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-07 : 05:33:30

Hi jen
You could use a while loop. I find that using a temp table with an identity column tends to simplify the process. I.e.

SELECT
IDENTITY(INT, 1, 1) AS rownum,
field1,
field2,
field3,
etc.
INTO
#tab
FROM
[query]

DECLARE @rownum INT
DECLARE @field1 NVARCHAR(50),
DECLARE @field2 NVARCHAR(50),
DECLARE @field3 NVARCHAR(50)

SET @rownum = 1
WHILE @rownum < (SELECT MAX(rownum) FROM #tab1) + 1
BEGIN
--initialise parameters
SELECT
@field1 = t.field1,
@field2 = t.field2,
@field3 = t.field3
FROM
#tab AS t
WHERE
t.rownum = @rownum

--Conditionally Execute commands
IF @field1 = 'a'
BEGIN
EXEC MyProc1
@field1,
@field2,
@field3
END
ELSE IF @field1 = 'b'
BEGIN
EXEC MyProc2
@field1,
@field2,
@field3
END

--Iterate
SET @rownum = @rownum + 1
END

Mark

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-08 : 00:31:10

Thanks Mark,

I guess i really need to use a table...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources