Wednesday, August 23, 2006

Obscure T-SQL Annoyance

We are moving toward the use of SQL Server 2005 at work, so I have been getting my head wrapped around things like stored procedures and triggers. I have been using SQL Server Management Studio to create my stored procedures on my test database.

I created a stored procedure tonight that would insert a new record in the database and return the new identity in an output parameter. My stored procedure looked like this...
Use testing
GO

CREATE PROCEDURE [dbo].AddName
@firstname varchar(20) = NULL,
@lastname varchar(30) = NULL,
@NameID int OUTPUT
AS
SET @NameID = 0 -- return zero if the insert fails

IF @firstname IS NOT NULL AND @lastname IS NOT NULL
BEGIN
SELECT @NameID = ID
FROM names
WHERE firstname = @firstname
and lastname = @lastname

IF @NameID IS NULL
BEGIN
INSERT INTO names (firstname, lastname)
VALUES (@firstname, @lastname)

SELECT @NameID = SCOPE_IDENTITY()
END
END
SELECT @NameID
GO

Everything that I did just would not work. I kept getting a NULL back for the output parameter value. My test went something like this....

DECLARE @NameID INT
EXEC AddName 'John', 'Smith', @NameID
SELECT @NameID

I used the Template Explorer and setup a new stored procedure on the test database using the template. It worked like a charm with the output parameter being properly populated when the stored procedure returned.

I finally figured it out a little while ago. That little light blue word called OUTPUT over on the right after the last parameter in the EXEC statement for the stored procedure. I completely missed that on the template code example. I did not realize that you had to include the direction declaration there since the procedure itself already designated the last parameter as an output parameter.

My EXEC statement should have looked like this to work properly...

EXEC AddName 'John', 'Smith', @NameID OUTPUT

Oh well... I still have my hair. :)

Technorati Tags: , , ,

Saturday, August 19, 2006

Nerd Factor

I came across the Nerd Quiz on another blog this afternoon and just had to find out my nerd factor after 20 years of using computers.

I am nerdier than 65% of all people. Are you nerdier? Click here to find out!

Sunday, August 06, 2006

MCPD - Microsoft Certified Professional Developer

Because of the way that we are headed at work, I have decided to go the Microsoft Certified Professional Developer route. To do this, I have to get the Microsoft Certified Technology Specialist (MCTS) certification first. I am going to do this on the .NET 2.0 Windows Applications path first and then the Web Applications path.

I ordered the MCTS Self-Paced Training Kit (Exam 70-536) late last week. I want to try and do this in the next six months to a year.