Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Cursor Help Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 2:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 13, Visits: 111
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
Posted Monday, September 10, 2012 3:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 2,379, Visits: 7,582
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" )!
Post #1356608
Posted Monday, September 10, 2012 4:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 13, Visits: 111
Thanks for your response, just what I needed.
Post #1356626
Posted Tuesday, September 11, 2012 3:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 941, Visits: 2,936
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

@SeanPearceSQL

About Me
Post #1357231
Posted Tuesday, September 11, 2012 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,082, Visits: 12,545
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1357463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse