Stupid Code Tricks

Auto Number / Sequence Generator in SQL Server

2011-06-07T06:38:00.000-07:00

This magic code will create a sequence of numbers that can be used like a primary key without having to use a table's IDENITITY column.

SELECT ROW_NUMBER() OVER (ORDER BY col1) AS rn, * FROM tbl1

Using CTE (Common Table Expression)

2009-06-16T10:33:00.000-07:00

To create a collection of rows without querying a table or a repeating series of INSERT statements.

declare @seed int
select @seed = 8;

WITH RecurseTable(Id)
AS
(
SELECT @seed

UNION ALL

SELECT R.Id -1
FROM RecurseTable R
WHERE R.Id > 1
)

select *
from RecurseTable

Querying Table Definitions

2009-04-24T07:41:00.000-07:00

A query to return metadata about a table. This is useful for dynamic query building like in a SqlAdapter, for example.


SELECT C.COLUMN_NAME,
       C.DATA_TYPE,
       C.CHARACTER_MAXIMUM_LENGTH,
       CASE WHEN PK.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END IS_KEY,
       CASE WHEN IC.object_id IS NULL THEN 0 ELSE 1 END IS_IDENTITY
FROM INFORMATION_SCHEMA.COLUMNS C
       LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK ON C.TABLE_NAME = PK.TABLE_NAME
                                      AND C.COLUMN_NAME = PK.COLUMN_NAME
       INNER JOIN
sys.tables T ON C.TABLE_NAME = T.name
            AND T.[type] = 'U'
       LEFT OUTER JOIN
sys.identity_columns IC ON T.object_id = IC.object_id
                       AND IC.[name] = C.COLUMN_NAME
WHERE C.TABLE_NAME = @TableName

Efficiently Rounding a SQL Server datetime

2008-10-06T08:04:00.000-07:00

Truncates hours, minutes and seconds without messy string manipulation:

CAST(FLOOR(CAST(GETDATE() AS DECIMAL(8,2))) AS datetime)

Underdocumented SQL Server mail parameter

2008-09-10T07:14:00.000-07:00

Due to popular demand, Microsoft added the @query_result_no_padding to the sp_send_dbmail stored procedure.

Sadly, you wont find this in the online help. It was added as part of a Service Pack.

Use this parameter to trim excess spaces from the query results attached to the email. Otherwise, the column output pads out to the length of the char OR varchar field.

For example:
declare @t TABLE (field varchar(10))

insert into @t
select 'hello'

When sent with @query_result_no_padding = 1
the result is:


field
-----
hello

otherwise


field
----------
hello

Decode HTML Strings in SQL Server

2008-06-06T12:38:00.000-07:00

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- EXEC UrlDecode '%c3%a1'
-- EXEC UrlDecode '\\Tul1ciprmw1\RichMediaAssets\Open\Telemundo\Al Rojo Vivo con Mar%c3%ada Celeste\_derived_jpg_q90_172x128_m1\3ARValx05Mar%c3%ada Celeste Arrar%c3%a1s.jpg'
-- EXEC UrlDecode '%4D'

ALTER PROCEDURE UrlDecode
-- Add the parameters for the stored procedure here
@URL varchar(2000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Position INT,
@Base CHAR(16),
@High TINYINT,
@Low TINYINT,
@NewHigh INT,
@Pattern CHAR(21)

SELECT @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@Position = PATINDEX(@Pattern, @URL)

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1

WHILE @Position > 0
BEGIN

IF (@High & 15) = 12 -- xC0
BEGIN
SELECT @NewHigh = @Low * POWER(2, 6) --Shift Low 6 bits

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 4, 1), @Base) -1
SELECT @Low = CHARINDEX(SUBSTRING(@URL, @Position + 5, 1), @Base) -1

SELECT @URL = STUFF(@URL, @Position, 6, CHAR(@NewHigh | (16 * @High) | @Low))

SELECT @High = 0, @Low = 0, @NewHigh = 0

END
ELSE
BEGIN
SELECT @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High | @Low))

END

SELECT @Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
BEGIN
SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1
END
END

SELECT @URL

END
GO

Selecting XPath Nodes

2007-01-10T05:52:00.000-08:00

To select a range of nodes based on their position, use the really cool position() function.

Using the code example below, this syntax will get the first two nodes:

var nodeList = xmlDoc.selectNodes("/root/items/item[position()<3]");

XPath Basics

2006-12-27T05:28:00.000-08:00

When querying an XML Document with XPath syntax, there are two things you need to identify; the node(s) you want to return and the criteria that determines which nodes qualify.

In this XML document:


<root>
   <items>
      <item>One</items>
      <item>Two</items>
      <item>Three</items>
   </items>
</root>

To get the item nodes using MSXML
var nodeList = xmlDoc.selectNodes("/root/items/item");

To apply selection conditions, add brackets:
var nodeList = xmlDoc.selectNodes("/root/items/item[.='One']");

The "." in the brackets means "this node". So in plain English you're saying, "Give me all the item nodes where the value is "One".

You can also select nodes based on the values of child or parent nodes.

Let's make the simple example above a little more complicated:


<root>
   <items>
      <item>One</item>
         <type>Odd Number</type>
      <item>Two</item>
         <type>Even Number</type>         
      <item>Three</item>
         <type>Odd Number</type>
   </items>
</root>

To get all the nodes which have a type value of "Odd Number" you would use this syntax:

var nodeList = xmlDoc.selectNodes("/root/items/item[./type='Odd Number']");

Notice here that the select path still goes to the item level. However, the query references the type node. The XPath path symbols are nearly identical to directory structure navigation syntax.

/ = levels
. = current node
.. = parent node
./ = child of the current node