SQL Cursor Help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » SQL Cursor Help

SQL Cursor Help

Rate Topic

Display Mode

Topic Options

Author

Message

DBayliss

Posted Monday, September 10, 2012 2:54 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 9:04 AM
Points: 10, Visits: 99

I need to amend the below Cursor to output into a table off which I can Query/join the output of the cursor etc . I'm new to cursors so any help and explanation would be greatly appreciated.

DECLARE @SQL varchar(MAX)
DECLARE @TABLENAME varchar(MAX)
DECLARE @Call_Cursor Cursor

SET @CALL_CURSOR = CURSOR FAST_FORWARD FOR
SELECT
[SO].[Name]
FROM sys.sysobjects AS [SO]
WHERE [SO].[Xtype] = 'U'
AND [SO].[name] NOT IN
(
'Queues'
,'Statuses'
,'Banned_Numbers'
,'SMS_Settings'
,'Predictive_Dialler_Detail'
,'Predictive_Dialler_Stats'
,'Blocked_Email_Addresses'
,'Contact_List'
,'CampaignSettings'
,'CampaignCall'
,'Extensions'
,'MessageSummary'
,'Files_Status'
,'Teams'
,'Agents'
,'convalues'
,'sysdiagrams'
)
ORDER BY [SO].[name]

OPEN @Call_Cursor

FETCH NEXT FROM @Call_Cursor INTO @TABLENAME
SET @SQL = ''
WHILE (@@FETCH_STATUS != -1)
BEGIN

SET @SQL = @SQL + 'SELECT ''' + @TABLENAME + ''' AS [User],CONVERT(Datetime,CONVERT(varchar(10),[Start_Time],103),121) AS [Date], CONVERT(Datetime,[Start_Time],121) AS [Start_Time],CONVERT(Datetime,[End_Time],121) AS [End_Time],[Record_Type],[User_Action],[Direction],[Action_Data],[From_addr],[To_addr],[Subject],[Detail],[Company],[Contact],[FullPath],[dbo].[svfGetFormattedTime] (CONVERT(Datetime,[Start_Time],121),CONVERT(Datetime,[End_Time],121)) AS [Duration] FROM [dbo].[' + @TABLENAME + '] WHERE [Record_Type] <> ''Predictive'' AND CONVERT(Datetime,[Start_Time],121) >= ''' + CONVERT(varchar(20),@DateFrom,113) + ''' AND CONVERT(Datetime,[Start_Time],121)<= ''' + CONVERT(varchar(20),@DateTo, 113) + ''' AND CONVERT(Datetime,[End_Time],121) >= ''' + CONVERT(varchar(20),@DateFrom,113) + ''' AND CONVERT(Datetime,[End_Time],121)<= ''' + CONVERT(varchar(20),@DateTo, 113) + ''''

FETCH NEXT FROM @Call_Cursor INTO @TABLENAME
IF @@FETCH_STATUS != -1
BEGIN
SET @SQL = @SQL + ' UNION ALL '
END
END
EXEC (@SQL)
SELECT @SQL
CLOSE @Call_Cursor
DEALLOCATE @Call_Cursor
END

Post #1356598

Cadavre

Posted Monday, September 10, 2012 3:24 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

Create a table, then change this: -

EXEC (@SQL)
SELECT @SQL

To this: -

INSERT INTO yourTable
EXEC (@SQL);

If you're doing this to learn the syntax for a cursor, then ignore the next part of this post. Otherwise, you could remove the cursor altogether by building your dynamic SQL directly and concatenating with FOR XML. If you need help doing this, let me know.

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1356608

DBayliss

Posted Monday, September 10, 2012 4:09 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 9:04 AM
Points: 10, Visits: 99

Thanks for your response, just what I needed.

Post #1356626

Sean Pearce

Posted Tuesday, September 11, 2012 3:28 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 350, Visits: 1,340

Note, @@FETCH_STATUS contains one of the following values:

0: The FETCH statement was successful.
-1: The FETCH statement failed or the row was beyond the result set.
-2: The row fetched is missing.

Therefore it's best to use

WHILE @@FETCH_STATUS = 0

http://thesqlguy.blogspot.com/

Post #1357231

Sean Lange

Posted Tuesday, September 11, 2012 8:27 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

Artoo22 (9/11/2012)

WHILE @@FETCH_STATUS = 0

I would suggest it is better to not use a cursor for this. BigGrin

A cursor is the slow way to get this data.

In those rare cases where a cursor is needed it is however much better to use the check like our little droid recommends.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1357463

« Prev Topic | Next Topic »

Permissions