Date Conversions in SQL

Does what it says on the tin. I find this a particularly handy trick to know when trying to copy & paste SQL data from Query Analyser into Excel, because the default date/time format causes Excel to scramble the dates into nonsense.

Stupid Excel.

Anyway, on with the show.


CONVERT(CHAR(19),GETDATE()) -- gives 31 March 2003 11:19
CONVERT(CHAR(11),GETDATE(),106) -- gives 31 March 2003


A SQL Split function

This is so useful it's beyond me why it isn't adopted as standard functionality. Maybe it is in SQL Server 2008. In the meantime, here's how I do it.


CREATE FUNCTION fnSplit(@Data nvarchar(4000),
@Delimiter varchar(10) = ',')
RETURNS @tblSplit TABLE

(ItemId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Item nvarchar(4000) NULL)

AS

BEGIN

DECLARE @Delimiter2 varchar(12),
@item nvarchar(4000),
@iPos int,
@DelimWidth int

--had to do this cuz if they send in a > 9 char delimiter I could not pre and post append the % wildcards
SET @Delimiter2 = @Delimiter
SET @Delimiter2 = ISNULL(@Delimiter2, ',')
SET @DelimWidth = LEN(@Delimiter2)

IF RIGHT(RTRIM(@Data), 1) <> @Delimiter2
SELECT @Data = RTRIM(@Data) + @Delimiter2

IF LEFT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = '%' + @Delimiter2

IF RIGHT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = @Delimiter2 + '%'

SELECT @iPos = PATINDEX(@Delimiter2, @Data)

WHILE @iPos > 0
BEGIN
SELECT @item = LTRIM(RTRIM(LEFT(@Data, @iPos - 1)))
IF @@ERROR <> 0 BREAK
SELECT @Data = RIGHT(@Data, LEN(@Data) - (LEN(@item) + @DelimWidth))
IF @@ERROR <> 0 BREAK
INSERT INTO @tblSplit VALUES(@item)
IF @@ERROR <> 0 BREAK
SELECT @iPos = PATINDEX(@Delimiter2, @Data)
IF @@ERROR <> 0 BREAK
END

RETURN
END



You call it like this:


select item from [reference]..fnSplit( @variable, ',' )


Where @variable is a varchar containing your comma-delineated string. Useful for sticking directly into IN clauses.

Reading XML with XPath

There's more than one way to read an XML file in dot net, but the XPath model doesn't seem to be terribly widely documented. Which is a shame because although it's a little unintuitive, it offers some functionality that doesn't come with other tools.


using System.Xml.XPath;

XPathDocument xDoc = new XPathDocument(“filepath”);
XPathNavigator xNav = xDoc.CreateNavigator();

Xnav.MoveToRoot();
Xnav.MoveToFirstChild();

do
{
//do stuff .. like xNav.GetAttribute(""AttributeName", "");
}
while (Xnav.MoveToNext());


So, load your xml file by replacing "filepath" with the path of the - ahem - file you want to load. MoveToRoot and MoveToFirstChild do exactly what you'd expect them to, and you can obviously vary these commands depending on where you want to start reading. Then the do-while loop should pass through each node in turn, allowing you to access its attributes and such.

SQL Cursors

SQL cursors, for those who don't know, are effectively a way of introducing a loop into a chunk of SQL code. Quite why the people who invented SQL couldn't call them "loops" I don't know - DBAs always have to be different, I suppose. Anyway it's a handy thing to know.

Beware though that SQL cursors are resource-intensive. If efficiency at the database end is important to you, it might be better to return the data and loop through it in the handling code. However, there are times when you need to get data, loop through it and then do something else at the DB end with the results of the loop. In those cases using a cursor is likely to be less intensive than the round trips involved in two separate back-and-forth trips to the database server.


Declare c1 cursor read_only
For
Select from
-- select statement picks up
--rows to sort through

Declare @trackingID int
-- declare a variable to hold the primary key
-- of the rows you’re returning

Open c1

Fetch next from c1 into @trackingID

While @@fetch_status = 0
Begin
--do something with the tracking variable,
--such as using it to select some
--data or do an update or somesuch

Fetch next from c1 into @trackingID
End

Close c1
Deallocate c1


Writing files to disc

This is a simple enough procedure but, in honesty, I always, always forget how to do it, and when you go and look it up on the interweb it seems that the examples provided are far more complicated than they really need to be. I guess it must be that the System.IO namespace simply isn't as intuitive as perhaps it should be. So here's the stripped down, basic code you need to write a file to disc - don't forget that the namespace goes at the top of your file, while the actual operational code goes inside it.


using System.IO;

FileStream myStream = new FileStream("insert File path here", FileMode.Create);
StreamWriter myWriter = new StreamWriter(myStream);

myWriter.Write("Some Text");
myWriter.Write(Environment.NewLine);
//this creates a new line. Duh!

myWriter.Close();
myStream.Close();


So put the target path into "insert File path here" and put your text into where it says "some text". Obviously you can keep adding as much text or new lines as you need.

Finding start of week in T-SQL

Wah! So, the first two posts turn out to be HTML posts! Well, probably because I'm not doing much actual dot net programming right now :). Besides, this was too neat not to document. It's a line of SQL commands which will determine the first day of the current week. Could be extended to find the first day of any given week.


declare @weekStart datetime
select @weekStart = dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())
select @weekstart


You can also put some more jiggery-pokery round it in order to get midnight on the first day of the week:


declare @weekStart datetime, @now datetime
select @weekStart = dateadd(dd, 0, datediff(dd, 0, dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())))
select @weekstart


Search Stored Procedures in T-SQL

So, I started a blog called "dot net notes" and my first post is about SQL. Doesn't look good but in honesty this is one of the most useful things I ever learned in SQL - how to do a blanket search of the text in all the stored procedures in a given database.

SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%text_to_search_for%'
ORDER BY SO.Name

Just replace "text_to_search_for" with, well, whatever you want to search for and you're good to go. Immensely helpful for tracking down chains of stored procedures which call each other, or examples of something you've done before in SQL but can't quite remember, or finding all the SP's in a database which reference a particular table or field. With enough tweaking it'd probably make you tea in the morning and wipe your arse for you.

Introduction

OK, well what's this for? I program for a living - mainly asp dot net, but also SQL and classic asp. For a while now I've been collecting notes on how to do certain things - some neat solutions to problems I've found, some things I come up against time and time again but can never remember how to do, some common "gotchas" I've come up against.

And I figured I'd move it into a blog.

Why? Well, partly because it's easier for me to index and find things in a blog format. Partly because it's stuff other people might use. So if you find something useful on here, just enjoy. No need to comment unless I've made a fundamental error :)