SQL Cursor Help

Question

Post reply

SQL Cursor Help

DBayliss

Mr or Mrs. 500

Points: 530
More actions
September 10, 2012 at 2:54 am

#267379

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

DBayliss Mr or Mrs. 500 Points: 530 More actions · Answer 2

DBayliss

Mr or Mrs. 500

Points: 530

September 10, 2012 at 4:09 am

#1535592

Thanks for your response, just what I needed.

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

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

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Artoo22 (9/11/2012)
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

I would suggest it is better to not use a cursor for this. 😀 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/