{"id":475,"date":"2015-12-27T21:02:54","date_gmt":"2015-12-28T03:02:54","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=475"},"modified":"2015-12-28T08:01:16","modified_gmt":"2015-12-28T14:01:16","slug":"t-sql-cursor-example","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=475","title":{"rendered":"T-SQL: Cursor Example"},"content":{"rendered":"

ARTICLE FROM ORIGINAL SITE – Young and Dumb!<\/strong><\/p>\n

Database Cursors have long been a favorite of mine<\/del>. It allows you to take a subset of data and output the information in various ways. I use them for such things as building the body of an automated email with a cursor set within a stored proceedure. You can return output to build a cleaner report output, etc.Example<\/p>\n

Select 'Case Created: '+convert(Varchar(20), @DATE ) + ' Case Number: '+ convert(varchar(10), @CaseNum)<\/pre>\n
We will start with the basic frame work and build from that:<\/p>\n
 We will use a two tables named SomeTable and Other Table
\n (The tables are actually from MySQL – I was lazy.)
\n<\/p>\n
# --------------------------------------------------------\r\n#\r\n# Table structure for table 'othertable'\r\n#\r\n\r\nCREATE TABLE othertable (\r\n   id bigint(20) NOT NULL auto_increment,\r\n   Eventid bigint(20) DEFAULT '0' NOT NULL,\r\n   Assignedto varchar(20) NOT NULL,\r\n   Timestamp timestamp(14),\r\n   PRIMARY KEY (id),\r\n   UNIQUE id (id)\r\n);\r\n\r\n\r\n# --------------------------------------------------------\r\n#\r\n# Table structure for table 'sometable'\r\n#\r\n\r\nCREATE TABLE sometable (\r\n   id bigint(15) NOT NULL auto_increment,\r\n   Event longtext NOT NULL,\r\n   EventDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,\r\n   Timestamp timestamp(14),\r\n   PRIMARY KEY (id)\r\n);\r\n\r\nThe two table( If they were real) would be joined by \r\nOtherTable.Eventid to Primary Key SomeTable.id.\r\n\r\n\r\n\r\n Okay the Cursor!\r\n\r\n--- This is optional, the cursor can run as a script or \r\n --- you can compile it as a stored proceedure \r\n --- Let's set a range for the system to lookup events\r\n \r\n CREATE PROCEDURE USER_DATA_CURSOR \r\n @StartDate DateTime, @EndDate DATETIME\r\n AS\r\n DECLARE MY_CURSOR Cursor --- We need to name It!\r\n\r\n --- We need to Tell the System what it is for\r\n --- We can do a complex SQl Statement with 16 joins \r\n --- or a simple one like this one.\r\n FOR \r\n Select ID, EventDate, Event\r\n From Sometable\r\n where EVENT in ('Reboot_System', 'Failed_Login','Failed_Service')\r\n and\r\n EventDate Between @StartDate and @EndDate\r\n\r\n\r\n Open My_Cursor  --- (remember to CLOSE IT LATER)\r\n --- We need to make containers for the Cursor Info\r\n DECLARE @VAR1Number DateTime, @VAR2DateTime DATETIME @VarLongText LongText\r\n\r\n Fetch NEXT FROM MY_Cursor INTO @VAR1Number, @VAR2DateTime, @VarLongText \r\n While (@@FETCH_STATUS <> -1)\r\n BEGIN\r\n IF (@@FETCH_STATUS <> -2)\r\n\r\n --- Here is where we put the fun stuff!!!\r\nTo add a blank Line\r\n Print \" \" \r\n -- This will display the the first row an on and on\r\n Select @VArNumber, @var2DateTime, @VarLongText \r\n\r\n---You Can Use the values to build a new query or look up additional values\r\n--- This displays the assignto field from OtherTable\r\n\r\n SELECT o.assignto, S.Event\r\n FROM OtherTable O, Sometable S \r\n WHERE o.eventid = s.id \r\n ORDER BY o.assignto\r\n\r\n--- You can even Convert the out put, put in conditional logic, etc.\r\n --- Once you are finished with the first record then you it will check for a new value.\r\n --- You can also supply logic to move back in forth in the cursor, etc.\r\n \r\n FETCH NEXT FROMMY_CURSOR INTO @VAR1Number, @VAR2DateTime ,@VarLongText \r\n END\r\n CLOSE MY_CURSORr\r\n DEALLOCATE MY_CURSOR\r\n GO\r\n\r\n\r\nTo run this stored procedure:\r\n Exec USER_DATA_CURSOR '1\/1\/2002', '3\/1\/2002'\r\n<\/pre>\n
 Well if the tables existed. <\/p>\n","protected":false},"excerpt":{"rendered":"
ARTICLE FROM ORIGINAL SITE – Young and Dumb! Database Cursors have long been a favorite of mine. It allows you to take a subset of data and output the information in various ways. I use them for such things as …<\/p>\n
Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[266,22,5],"tags":[218,309,303,249,35,36,295,52,291],"class_list":["post-475","post","type-post","status-publish","format-standard","hentry","category-dba","category-sql-server","category-t-sql","tag-cursor","tag-cusor","tag-dba","tag-script","tag-select","tag-sql","tag-sql-server","tag-ssms","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/475","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=475"}],"version-history":[{"count":6,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/475\/revisions"}],"predecessor-version":[{"id":484,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/475\/revisions\/484"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}