This blog is now at htack210.wordpress.com.

Please meet me there! Thanks!

There’s more than one way to skin a query. Yesterday I put up a code snippet showing how to get a return value from a stored proc using a SELECT statement.

Another way to do the same thing is to use an OUTPUT parameter. You create your parameters as you normally would, but for the return value you declare the parameter like this:
@CustomerID int OUTPUT

At the end of your TRY block you set the OUTPUT variable like this:

SET @CustomerID = SCOPE_IDENTITY();

To test it, declare the OUTPUT value:

DECLARE @CustomerID int

Next, use an EXEC statement to populate your variables:

EXEC    @return_value = [dbo].[spAddCustomer]
@CustomerFirstName = N'Betty',
@CustomerLastName = N'Rubble',

…. (more params here)…

SELECT @CustomerID as N’@CustomerID’

The SELECT statement will return a column called @CustomerID with the new customer number in it.

For our PiggyBank project we need to create a stored proc that lets us add a customer. Since the CustomerID field is an Identity column and automatically creates a new ID for new customers, I thought it would be a good idea to return that value so that the front end program calling it could use the new ID. So, I added this line to my TRY block:

SELECT SCOPE_IDENTITY() as CustID

SCOPE_IDENTITY grabs information from the Identity column and brings back the identity for the last inserted row. the ‘as CustID’ part is not necessary.

When this proc is used, it returns the CustomerID in the SELECT statement.

We are working on Piggy Bank project this week. One of the extra credit sections involves shredding an XML file so that transaction information is inserted into a table called Transactions. I love a challenge so I gave it shot!

Figuring out the paths in order to navigate the files was easy enough. It reminded me of typing in DOS paths or setting up links to external CSS files in HTML. The only real problem had to do with the TransactionType field in the XML file; it was a string field (vchar(20)). The corresponding field in the SQL table was the TransactionTypeID field; it was an int datatype. The trick was taking what the XML file held and that field and translating it into some the the corresponding SQL table field could use.

To solve the problem, I used a SELECT CASE statement:

insert into dbo.Transactions (TransactionID, AccountID, TransactionTypeID, CustomerID, TransactionDate, TransactionAmount, NewBalance)
Select TransactionID
, AccountID
, CASE TransactionTypeID
WHEN 'Initial Deposit'        THEN 1
WHEN 'Deposit'                THEN 2
WHEN 'Withdraw'               THEN 3
WHEN 'Transfer-Withdrawal'    THEN 4
WHEN 'Transfer-Deposit'       THEN 5
WHEN 'Checkcard Purchase'     THEN 6
WHEN 'Check'                  THEN 7
END
, CustomerID
, TransactionDate
, TransactionAmount
, NewBalance

from OPENXML (@docHandle, ‘/Bank/Customer/Accounts/*/Transactions/Transaction’, 1)
with (TransactionID              int ‘@ID’
, AccountID int ‘../../@ID’
, TransactionTypeID            varchar(20) ‘@Type’
, CustomerID                            int ‘../../../../@ID’
, TransactionDate                   date ‘@Date’
, TransactionAmount            money ‘@Amount’
, NewBalance                            money ‘@NewBalance’
)

The CASE portion of the code is pretty much self explanatory. Basically, every time it found a particular term like ‘withdraw’ in the XML file, it would insert the corresponding ID in the SQL table.

It was fun trying to get it figured out and pretty simple in the end. Since we needed that table to do the rest of the project, it was also a real time saver!

I am a former school teacher. In my last teaching position I had the great privilege of teaching Junior/Senior high students with special needs at Hidden Treasure Christian School (HTCS). One of the best lessons I learned there was this: Whatever you can do to help a child with special needs learn a new concept improves you as a teacher and a communicator. I love teaching and I love learning which is why I have always enjoyed computing and technology. There is always something new to learn , new horizons to reach, and new problems to solve.

While at Hidden Treasure I used my computing skills and my knowledge of relational databases to create a cross-platform, network-based database to maintain student transcripts. It keeps track of student grades, progress reports, IEP’s, etc.

Now I am making a career change and while I have experience well suited to the IT industry, I have quickly learned that experience is only one half of the equation. What employers want to see in addition to experience, particularly if you are on the outside of the IT world and trying to get in, is credentials. They want to see proof of your experience. Without it, you’re like the scarecrow in the Wizard of Oz: you might have the smarts, but you don’t have the piece of paper to prove it.

So why did I choose SetFocus? Actually, I found them by accident. I was searching the job boards to see where I might be able to put my skills to use in the IT world and came across ads for SetFocus. So I did some digging and researching. When I made contact with them, I was allowed to “sit in” on classes in session so that I could see for myself whether or not SetFocus was for me. I was impressed with what I saw and equally impressed with the people I contacted with questions. They were honest and up front with me about everything. So I decided to take the plunge.

I started class in November and here are some of the reasons I continue to believe I have made the right choice:

• Knowledgeable faculty. Being a teacher myself, I naturally critique those who teach me. The faculty are not just facilitators who walk you through the curriculum and hope that you get it. These people have real world experience and shows in their knowledge and their teaching.
• Excellent job placement help (more on that later).
• Convenience. SetFocus offers day or evening classes. I opted for the evening classes because the pace is a little slower so I can digest what I am learning, and so I can continue working in the day time.
• Great location. Because of the GRID, I can take all of my classes online and go to a live classroom from home.

Just in case you are wondering, this is NOT a paid advertisement, and I will not get a better grade for anything I am saying. This is my own opinion based on first-hand experience!