Blog ala Vidar

A couple of days ago Torgeir came over to my desk and told me proud “you shouldn’t use SELECT when setting variables. You should use SET”. Then he told me the reason, and it got me thinking.. The reason is simple. If you do this:

DECLARE @PrimKey AS UNIQUEIDENTIFIER
SELECT @PrimKey = PrimKey
	FROM stbl_WinClient_Projects
	WHERE ProjectName = 'Sys.Security.ManageUsers'
PRINT @PrimKey
SELECT @PrimKey = PrimKey
	FROM stbl_WinClient_Projects
	WHERE ProjectName = 'A.Project.That.Does.Not.Exist'
PRINT @PrimKey

Notice that it will return the same PrimKey both times. When you’re writing the query, you might think that this will give you NULL on the last SELECT, but it won’t because there’s no records returned, and it will therefore not touch the variable. The correct way of doing this would then be:

DECLARE @PrimKey AS UNIQUEIDENTIFIER
SET @PrimKey = (SELECT PrimKey
		FROM stbl_WinClient_Projects
		WHERE ProjectName = 'Sys.Security.ManageUsers')
PRINT @PrimKey
SET @PrimKey = (SELECT PrimKey
		FROM stbl_WinClient_Projects
		WHERE ProjectName = 'A.Project.That.Does.Not.Exist')
PRINT @PrimKey

In some cases you might want to add ISNULL. For instance in subselects, this might be important. In the next query you’ll notice what I’m talking about. The first column will return NULL, and the other will return 0.000000:

SELECT
  (SELECT TOP 1 ISNULL(Amount, 0) AS Amount
	FROM atbv_Accounting_Transactions
	WHERE Series = 'SomethingThatDoNotExists'
	ORDER BY Period DESC) AS Amount1,
  ISNULL((SELECT TOP 1 Amount AS Amount
	FROM atbv_Accounting_Transactions
	WHERE Series = 'SomethingThatDoNotExists'
	ORDER BY Period DESC), 0) AS Amount2

Both queries are using ISNULL, but the first is using INSIDE the subquery, but since this will never return any records, it will never come that far.. The second uses ISNULL OUTSIDE the subquery, so if the subquery returns NULL, it will replace it with 0.0000

NB! One last thing… Some bright souls may have noticed that I haven’t used   NOLOCK on the first queries. I’ll send you a price. Good boy/girl 😀 ALWAYS REMEMBER TO USE WITH (NOLOCK) WHEN SELECTING FROM TABLES!

This is a new feature to SQL Server 2008, where you can send a table to a SP or UDF. I’ve got the question about how to handle this in 2005 many times, and I’ve now decided to post a blog about it. Before you read more about this, please consider. IS IT REALLY NECCESSARY to do this from the client? Can we do everything on the SQL Server? If so, DO IT! My following example can all be done on the server, but this is only to show you how to do it if you really need to get it from the client.

Let’s say we got a bunch of data we want to send to a SP. In this case, I’ve just built up a dataset from stbv_System_Users, where I’ve got one column, Login.

Dim vUsersDataSet As New DataSet
Dim vUsers As New afRecordSource("stbv_System_Users")

vUsers.SelectColumns.Add("Login")
vUsers.MaxRecords = 10

vUsersDataSet.Tables.Add(afAccessLayer.GetData(vUsers))

Dim vProcedure As New AppFrame3.Common.Data.afProcedureCall
vProcedure.ProcedureName = "astp_Vidar_Test1"
vProcedure.Parameters.Add("MyXML", vUsersDataSet.GetXml().ToString())

Try
	AppFrame3.Common.Data.afAccessLayer.ExecProcedure(vProcedure)
Catch ex As Exception
	AppFrame3.Win.CR3.HandleException(ex)
End Try

Here you see I’ve put it in a dataset, then used .GetXml to send it as a parameter to my SP:

CREATE PROCEDURE astp_Vidar_Test1 (@MyXML AS XML)
AS

DECLARE @DocID AS INT

EXEC sp_xml_preparedocument @DocID OUTPUT, @MyXML

SELECT *
	FROM OPENXML(@DocID, '/NewDataSet/Table1', 2)
	WITH (Login NVARCHAR(128))

EXEC sp_xml_removedocument @DocID

My SP takes one parameter, and “converts” it to SQL using OPENXML. Now I could go on, inserting these records somewhere. OPENXML takes three parameters. First one is docid, which is created by sp_xml_preparedocument. Second one is the path in the XML you want to handle. Third one is what type of input it is. If it’s with elements or attributes. 2 is elements, 1 is attributes. Under you’ll see the XML created by the .GetXml method on the dataset. I don’t care about the PrimKey field, so I’ve only specified the Login field in the WITH clause.

<NewDataSet>
  <Table1>
    <Login>vidar</Login>
    <PrimKey>63444aa2-e0b8-4c89-b476-229c81145961</PrimKey>
  </Table1>
  <Table1>
    <Login>vidar2</Login>
    <PrimKey>e72d74d0-3c8f-4256-b188-8001557d9849</PrimKey>
  </Table1>
</NewDataSet>

When doing QA on SQL-objects, I’ve come over some very common mistakes. Number one has to be people forgetting that the Inserted and Deleted views inside triggers may contain more than more record.
This is a very good (and simplyfied) example of code from an insert trigger:

DECLARE @Login AS NVARCHAR(128)

SELECT @Login = Login
	FROM Inserted

IF NOT EXISTS (SELECT * FROM stbl_System_Users WITH (NOLOCK) WHERE Login = @Login)
BEGIN
	INSERT INTO stbl_System_Users (Login) VALUES (@Login)
END

This will only work if you’re inserting one record, but what if you’re inserting several? It will only do the code you’ve added for ONE (random) record.
The solution for this is very simple:

INSERT INTO stbl_System_Users
  (Login)
SELECT
  I.Login
	FROM Inserted AS I
	WHERE NOT EXISTS (SELECT *
				FROM stbl_System_Users WITH (NOLOCK)
				WHERE Login = I.Login)

In some cases you might want to execute a SP. Instead of INSERT, then EXEC:

DECLARE @Login AS NVARCHAR(128)

SELECT @Login = Login
    FROM Inserted

IF NOT EXISTS (SELECT * FROM stbl_System_Users WITH (NOLOCK) WHERE Login = @Login)
BEGIN
    EXEC astp_Some_SP @Login = @Login
END

Solution to this:

DECLARE @Login AS NVARCHAR(128)

SELECT  I.Login
	INTO #LoopLogins
	FROM Inserted AS I
	WHERE NOT EXISTS (SELECT *
				FROM stbl_System_Users WITH (NOLOCK)
				WHERE Login = I.Login)

WHILE EXISTS (SELECT * FROM #LoopLogins)
BEGIN
	SELECT TOP 1
	  @Login = Login
		FROM #LoopLogins

	EXEC astp_Some_SP @Login = @Login

	DELETE
		FROM #LoopLogins
		WHERE Login = @Login
END