Cursorless Cursors - SQL Server Forums

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)

Cursorless Cursors

Author

Topic

anand_d
Starting Member

9 Posts

Posted - 2006-07-26 : 11:18:52

Hey guys,

I am using the following code to implement cursorless cursor. The idea is to fetch all the dates from a given table and call another stored proc to execute something for each given date. The same is done by avoiding cursors in the following method. Wanted to know if this is an optimal solution:

------------------------------------------------------------------
DECLARE @CROW INT, @MAXROW INT, @CREATEDATE DATETIME
DECLARE @SQL NVARCHAR(1000)
SET @CROW=1
SELECT @MAXROW=COUNT(1) FROM MYDB..CREATEDATE_TOREFRESH
SELECT @CROW, @MAXROW

WHILE @CROW <= @MAXROW
BEGIN
SET @SQL=N'(
SELECT @CREATEDATE=CONVERT(VARCHAR, MAX(CREATEDATE), 101) FROM MYDB..CREATEDATE_TOREFRESH
WHERE CREATEDATE IN
(
SELECT TOP '+ CONVERT(VARCHAR, @CROW, 101) + ' CREATEDATE FROM MYDB..CREATEDATE_TOREFRESH
)
)'
EXECUTE MASTER.[DBO].SP_EXECUTESQL @SQL, N'@CREATEDATE DATETIME OUTPUT', @CREATEDATE OUTPUT
PRINT CONVERT(VARCHAR, @CREATEDATE, 101)
SET @CROW=@CROW + 1
END
------------------------------------------------------------------

Thanks
Anand

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 11:26:28

not sure what you are doing but consider

declare @CREATEDATE DATETIME, @maxCREATEDATE DATETIME
select @CREATEDATE = '19000101' ,
@maxCREATEDATE = max(CREATEDATE) FROM MYDB..CREATEDATE_TOREFRESH

while @CREATEDATE < @maxCREATEDATE
begin
select @CREATEDATE = min(CREATEDATE) from MYDB..CREATEDATE_TOREFRESH where CREATEDATE > @CREATEDATE
PRINT CONVERT(VARCHAR, @CREATEDATE, 101)
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-26 : 11:28:56

It doesn't have any advantage over using a cursor, and will likely be slower than a cursor.

CODO ERGO SUM

anand_d
Starting Member

9 Posts

Posted - 2006-07-26 : 12:56:59

NR,

Just Superb. This is effective coding. Now, I know that experts like you can easily resolve issues just like that. The alternate code worked perfectly.

Yeah, lot of learning can be done with people like you in the forum.

Again, thanks a ton.

Regards,
Anand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources