Tuesday, 10 August 2010

Passing a TABLE variable into dynamic SQL

A question popped up on StackOverflow today, asking how to pass a TABLE variable into a dynamic SQL statement in SQL Server 2008. I've previously blogged about table-valued parameters, comparing the approach of passing in a TABLE of values to a stored procedure to the techniques you'd have had to use in earlier versions of SQL Server (e.g. CSV or XML), but this specific question is worth a quick follow-up.

Passing a TABLE variable in to a dynamic SQL statement is straight forwards:
-- First define a custom TABLE type
CREATE TYPE IntegerTableType AS TABLE (ID INTEGER)
GO

-- Fill a var of that type with some test data
DECLARE @MyTable IntegerTableType
INSERT @MyTable VALUES (1),(2),(3)

-- Now this is how you pass that var into dynamic statement
EXECUTE sp_executesql N'SELECT * FROM @MyTable', 
    N'@MyTable IntegerTableType READONLY', 
    @MyTable

Job done.

Thursday, 5 August 2010

The importance of being earnest

I've been learning and trying out a lot of new things recently and it got me to thinking just how important it is in this business, to continue pushing yourself and adding more strings to your bow. It can be easy to stay within your comfort zone and go with the flow, but there (usually) comes a time when you have to branch out for one reason or another.

Stand-ups, Tomatoes and a date(-abase) with Cassandra
Over the past week we've started doing daily stand-ups at work - still in the early stages but it already feels like I've been doing it for longer. Then there's the Pomodoro Technique which is a time management technique. Again, early days, but my initial experience has been very positive - I'm planning on swotting up for some SQL Server exams when I have time, and think this will come in very handy for that so is not just something I'll be using at work.

Something that has taken a lot of my time recently has been research into "NoSQL" technologies (or better described, "NoACID"). This is something that is outside of what I'd call my comfort zone - SQL Server. I've spent my time trying to learn more about all the different options out there to see what could provide a good fit to our scenario/requirements - Cassandra, MongoDB, CouchDB, RavenDB, HyperTable, HBase....to name but a few.

But what I've found in having time spent learning about these options, is that it's also pushing me to learn more about SQL Server. There's always something more to learn in the world of SQL Server, and the community around it is awesome - so I'm using the challenge of researching into NoSQL as a way to challenge my knowledge of SQL Server as that is a key part of my skillset that I'm always looking to keep maximising.

Questions, questions, questions
Answering questions, originally mainly on Experts Exchange and now mainly on StackOverflow, not only gives the opportunity to help others but also is a great way to learn more myself. Another important factor for me, is that it serves as a great record of what I have to offer technically . Everything is there for all to see - the good answers AND the not-so-good answers that I've gone on to learn from. I like the "honesty" of having it there, unfiltered.

What is my point?
Bit of a rambling of a blog post, it is a bit off-the-cuff. But there is a point. Being earnest, working hard at your career, trying to contribute to the wider community is an important aspect of being in this line of work. I'm a firm believer of trying to maximise the value I can offer to an employer. Trying to make yourself stand out from the crowd, especially in the recent economic climate can put you in good stead. I'm sure everyone has their own dream job at a dream company - so maximise your chance of getting there. May take a lot of effort, may take a lot of time....and the dream role may not exist or the opportunity may never arise, but it'd at least be an educational journey to go on and you can help others along the way.

It takes time to build up a reputation and get yourself known, so start early and stick with it.