t-sql looping without cursor

Many times I need to loop on data in sql server.  A cursor is not the best performing most times.  The main idea is to add a processed bit field to your temp table.

Technorati Tags: ,

Here is how a loop thru data:

DECLARE @tblData table(rowid int,activity varchar(50), startTime time, endTime time, processed bit) 
insert INTO @tblData VALUES (1,'Act. A','8:00','8:15',0) 
insert INTO @tblData VALUES (2,'Act. B','8:15','8:45',0) 
insert INTO @tblData VALUES (3,'Act. C','8:00','9:45',0) 
insert INTO @tblData VALUES (4,'Act. D','8:15','10:45',0) 
 
Declare @dID Int 
WHILE EXISTS (SELECT * FROM @tblData WHERE Processed = 0) 
 BEGIN 
 --get current row 
 SELECT @dID = MIN(rowid) FROM @tblData WHERE Processed = 0 
 
 
 UPDATE @tblData SET Processed = 1 WHERE rowid = @dID 
 END 
 
SELECT * from @tblData

Overwrite JS Alert

I recently needed to create a popup that had more than the standard OK button, so after some searching I stumbled upon a script to overwrite the window.alert method with a custom method.

JS Function
  1. <script type="text/javascript">
  2.                 // Overwrite the alert() method
  3.                 window.alert = function (sMessage, title, msgtype) {
  4.                         if (title == undefined) {title = 'Alert:';}
  5.                         if (msgtype == undefined) {msgtype = 'info';}
  6.                 //create jqueryui dialog
  7.                         var alertDialog = $('<div id="msgDialog"><div>' + sMessage + '</div></div>');
  8.                         alertDialog.dialog({
  9.                                 modal: true,
  10.                                 title: '<span class="ui-icon ui-icon-'+ msgtype +'" style="margin-right: 0.3em; float: left;"></span> ' + title,
  11.                                 buttons: {
  12.                                         Ok: function () {
  13.                                                 $(this).dialog("close");
  14.                                         }
  15.                                 }
  16.                         });
  17.                         return false;
  18.                 };
  19.         </script>
Usage:
  1. alert('At least 1 booking must be selected!!', 'Missing Parameter','alert');

For the third parameter I am passing in the last part of the ui-icon name, ie. alert, info, notice.

That’s about it, this is probably more for me to remember than to blog!

Useful JavaScript prototypes

I am always looking for things to make my life (job) easier.  I am constantly formatting dates.  Today I stumbled upon some examples for prototypes and just figured I will store them here for future reference.

 

Date.prototype.customFormat = function (formatString) {
var YYYY, YY, MMMM, MMM, MM, M, DDDD, DDD, DD, D, hhh, hh, h, mm, m, ss, s, ampm, dMod, th;
YY = ((YYYY =
this.getFullYear()) + "").substr(2, 2);
MM = (M =
this.getMonth() + 1) < 10 ? ('0' + M) : M;
MMM = (MMMM = [
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"][M - 1]).substr(0, 3);
DD = (D =
this.getDate()) < 10 ? ('0' + D) : D;\n DDD = (DDDD = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"][this.getDay()]).substr(0, 3);
th = (D >= 10 && D <= 20) ?
'th' : ((dMod = D % 10) == 1) ? 'st' : (dMod == 2) ? 'nd' : (dMod == 3) ? 'rd' : 'th';
formatString = formatString.replace(
"#YYYY#", YYYY).replace("#YY#", YY).replace("#MMMM#", MMMM).replace("#MMM#", MMM).replace("#MM#", MM).replace("#M#", M).replace("#DDDD#", DDDD).replace("#DDD#", DDD).replace("#DD#", DD).replace("#D#", D).replace("#th#", th);

h = (hhh =
this.getHours());
if (h == 0) h = 24;
if (h > 12) h -= 12;
hh = h < 10 ? (
'0' + h) : h;
ampm = hhh < 12 ?
'am' : 'pm';
mm = (m =
this.getMinutes()) < 10 ? ('0' + m) : m;
ss = (s =
this.getSeconds()) < 10 ? ('0' + s) : s;
return formatString.replace("#hhh#", hhh).replace("#hh#", hh).replace("#h#", h).replace("#mm#", mm).replace("#m#", m).replace("#ss#", ss).replace("#s#", s).replace("#ampm#", ampm);
};
Date Usage:
var now = new Date();
alert(
"The Current Date is " + now.customFormat('#MM#/#DD#/#YYYY#') + "\\nThe Current Time is " + now.customFormat('#h#:#mm##ampm#') + ".");