Generating Row Numbers in SQL

Use of the ROW_NUMBER() function to generate sequential numbers

This is one that I use regularly, and forget everytime what the syntax is.

It is really useful to be able to list rows of data for SQL Server with a sequential number on each row, which can be done via the ROW_NUMBER() function...

SELECT [ValCol], ROW_NUMBER() OVER(ORDER BY [OrderCol]) AS [RowNum]
FROM [MyTable]

This could result in...

ValCol  RowNum
First   1
Second  2
Third   3

There is also the ability to split/partition the sequentail numbers based on specific columns...

SELECT [ValCol], [SplitCol],
  ROW_NUMBER() OVER(PARTITION BY [SplitCol] ORDER BY [OrderCol]) AS [RowNum]
FROM [MyTable]

The sequence numbering now restarts for each unique [SplitCol] value...

ValCol  SplitCol  RowNum
First   One       1
Second  One       2
Third   Two       1

Show/Hide via Checkbox and CSS

Using hidden checkbox and CSS to control element visibility

Normally when we want to show/hide an element, we utilise jQuery...

<div id="trigger">Click Me</div>
<div id="showhide">Show/Hide</div>

$(function() {
  $("#trigger").on("click", function() {
    $("#showhide").toggle();
  });
});

The downside to this is when you start using responsive layouts, and you always want the element to be displayed in one state (e.g. desktop) but show/hide in a different state (e.g. mobile).  It is possible to end up in a situation where the item is hidden even in the state where you always want it visible.

I've recently seen a different way of doing it that gets around this problem, that uses hidden checkbox and pure CSS...

<div><label for="trigger">Click Me</label></div>
<input type="checkbox" id="trigger" style="display:none" />
<div id="showhide">Show/Hide</div>

#showhide { display:none; }
#trigger:checked ~ #showhide { display:block; }

The key here is ~ which effectively says "find the second part only if it is within the same parent as the first part and after the first part".

Javascript Nested Loop Break

How to break out of nested loops in javascript

In order to break out of a loop early in Javascript, we can use the break keyword which will terminate the current loop...

for (var i = 0; i < firstArray.length; i++) {
  if (firstArray[i] == myVal) {
    // Do something
    break;
  }
}

Occasionally I need nested loops, with the requirement to break out of both loops on a particular situation.

This can be achieved by using labels...

outerlabel:
for (var i = 0; i < firstArray.length; i++) {
  for (var j = 0; j < secondArray.length; j++) {
    if (firstArray[i] == secondArray[j]) {
      // Do something
      break outerlabel;
    }
  }
}

(Thanks to this answer on stackoverflow)

CSS "nth-child" Processing

How to apply CSS styles to complex selection of child elements

I can never remember the format of the nth-child CSS selector, so this is a quick summary...

The 2nd element in the parent that is a div...

div:nth-child(2) { ... }

Every 3rd element in the parent that is a div...

div:nth-child(3n) { ... }

Every 4th element in the parent that is a div, starting on the 5th one...

div:nth-child(4n+5) { ... }

Every child element in the parent this is a div, starting with the 6th one...

div:nth-child(1n+6) { ... }

You can also use nth-child(odd) and nth-child(even) instead of nth-child(n2+1) and nth-child(n2) respectively.

Note, div:nth-child(3) will not match the third div in the parent... it will match if the third element of the parent is a div.

You can also use this CSS checker to test

Client-Side Script After UpdatePanel Refresh

How to run client-side javascript after running an UpdatePanel refresh

In ASP.Net, the <asp:UpdatePanel> allows us a quick an easy way to refresh screen content with server-side changes without the need for a full page post-back.

It is very useful to be able to run javascript on the client once the refresh has happened, and this can be achieved using the following code...

If ScriptManager.GetCurrent(Page).IsInAsyncPostBack Then
    Dim script as string = "callFunction();"
    ScriptManager.RegisterStartupScript(Me.Page,
        Me.Page.GetType(), "script", script, True)
End If

UpdatePanel Button Click via Javascript

How to trigger a button click causing an UpdatePanel refresh

In ASP.Net, the <asp:UpdatePanel> allows us a quick an easy way to refresh screen content with server-side changes without the need for a full page post-back.

Sometimes it's necessary to trigger that refresh by using javascript, and in order to do that, we can use the following...

__doPostBack(document.getElementById("btnRefresh").name, "");

Or with jQuery...

__doPostBack($("#btnRefresh").prop("name"), "");

This is assuming that the <asp:Button runat="server" id="btnRefresh" /> is contained within the UpdatePanel, and that the button has not been set as a <Triggers><asp:PostBackTrigger/></Triggers>.

ASP.NET Fire Validation

Manually firing a single validator using client-side script

In ASP.Net the use of validators is common place, including the <asp:CustomValidator>.

If a validator is linked to a control, then that validator is fired when the control is changed... but what about when multiple controls are used on a single custom validator?  How do you make sure the validator shows the correct value when you change any of those controls?

One answer is to make all those controls call an extra function... which itself then uses the inbuilt ValidatorValidate() provided by ASP.Net.

Markup...

<asp:TextBox runat="server" id="txtFirst" onchange="textboxChanged();" />
<asp:TextBox runat="server" id="txtSecond" onchange="textboxChanged();" />
<asp:CustomValidator runat="server" ID="cusVal" ErrorMessage="Oops"
    ClientValidationFunction="doCustomVal" />

Javascript...

function textboxChanged() {
  ValidatorValidate(document.getElementById("<%=cusVal.ClientID%>"));
}
function doCustomVal(src, args) {
  args.IsValid = (
    document.getElementById("<%=txtFirst.ClientID%>").value != "" &&
    document.getElementById("<%=txtSecond.ClientID%>").value != "");
}

SQL Server CSV From Data

A powerful way to convert row-level data to CSV

The ability to convert row-level data into CSV values can be exceedingly useful.

Using the STUFF function in combination with the FOR XML PATH('') command is a powerful way of achieving this.

DECLARE @OUTPUT VARCHAR(1000)
SELECT @OUTPUT = STUFF(
  (SELECT ',' + MyStringVal
   FROM MyTable
   WHERE MyIntVal = SomeIntVal
   FOR XML PATH('')
  ), 1, 1, '')

The STUFF function "inserts a string into another string", and in this case is purely used to remove the leading comma.

The FOR XML PATH('') is the key, and takes row-level data and turns it into a string of XML data. By using PATH('') we're telling SQL to not wrap the XML with a root node.

Finally, the use of ',' + MyStringVal results in the column having no name... which means SQL will not wrap the individual values in an XML node either.

SQL Server Dynamic Script Permissions

Dealing with permissions when modifying data using sp_executesql

Thanks to the impending GDPR we have been implementing a series of changes to our systems, which use Microsoft SQL Server (2008+)

To aid in the speed of development, I have written a bunch of SQL scripts which require the use of sp_executesql commands.  What I had forgotten, however, is the small matter of permissions.

When you run a stored procedure (sproc), the permissions used are those of the user who created the sproc.  Which is great and means that you can run that sproc via a restricted access login.

The problem is that if the sproc uses sp_executesql to run dynamic script, the permissions switch to the restricted access login... the result being that you cannot SELECT, INSERT, UPDATE, DELETE.

The solution we're using involves creating a local WITHOUT LOGIN database user...

CREATE USER dynamicdatauser WITHOUT LOGIN
EXEC sp_addrolemember 'db_datareader', 'dynamicdatauser'
EXEC sp_addrolemember 'db_datawriter', 'dynamicdatauser'

And then allocating that user directly to the sproc...

CREATE PROCEDURE dbo.[MySproc]
  @PARAMS INT
WITH EXECUTE AS 'dynamicdatauser'
AS

To the best of my knowledge, giving the user db_datareader and db_datawriter permissions should not have any security implications as the WITHOUT LOGIN should block any ability for the account to be used outside of the code environment.

Purpose of this blog

Firstly, what this blog is not going to be is a diary.

Instead I will be using this blog as way of storing information, especially when it comes to technical solutions.

As a profession computer programmer (and part time DBA/SysAdmin) it is a daily occurrence that I need to figure out technical solutions. And being a human, I regularly forget what those solutions were.

So this blog is a way for me to quickly find the answer I've already worked out once, and hopefully other people on the web will find them as well.