Tip of the Day : SQL Dynamic Variables in Stored Procedure

Writing useful and reusable stored procedures is something I struggled with when first learning SQL. Not knowing anything about Execution Plans, I would often nest several “IF” statements in my procedure to execute different statements based on the parameters I wanted to pass in. This was my solution for dealing with a situation where I wanted to omit some parameters when I called the procedure, but didn’t know how to exclude them from my statements.

As I learned more and more about the language, I realized there were a couple different ways you could handle this situation and have settled with the following. Include all the parameters in your Where Clause, but also check to see if they’re null. Doing so allows you to use all the parameters at once, none of them, one of them, or any number in between.

Here is an example stored procedure built on the AdventureWorksLT database, which can be downloaded here. As always, this may or may not be the best practice solution, it’s just the solution I tend to use most. I’m always open to suggestions and debates on the the things I posted in the TOTD.

Create Procedure SalesLT.pCustomerAddressSearch
@City nvarchar(30) = null,
@StateProvince nvarchar(50) = null,
@CountryRegion nvarchar(50) = null,
@PostalCode nvarchar(15) = null

AS

Select
FirstName, LastName, AddressLine1, AddressLine2, City, StateProvince, CountryRegion, PostalCode
From
SalesLT.Customer C
Inner Join
SalesLT.CustomerAddress CA on CA.CustomerID = C.CustomerID
Inner Join
SalesLT.Address A on A.AddressID = CA.AddressID
Where
(City = @City or @City is null) AND
(StateProvince = @StateProvince or @StateProvince is null) AND
(CountryRegion = @CountryRegion or @CountryRegion is null) AND
(PostalCode = @PostalCode or @PostalCode is null)

Which then allows execution of the stored procedure in multiple ways.

Exec SalesLT.pCustomerAddressSearch @City = 'Ottawa', @PostalCode = 'K4B 1T7';

Exec SalesLT.pCustomerAddressSearch @City = 'Ottawa';

Exec SalesLT.pCustomerAddressSearch;

As I understand it this allows SQL Server to compile and save a proper execution plan which is not regenerated every time you run the procedure as can be the case when you’re placing IF statements in your procedures.

Better TOTD Posts

I spent a few minutes searching for a better way to post code on WordPress posts and found a pretty elegant solution. I took a few more minutes to update the existing TOTD posts, so you can now actually copy and paste them and have them functional without changes.

Sweeeeet.

Feedback Not Required

“You’re great at what you do and that’s why we hired you. But we don’t need any feedback from you on the best way to do your job. We’ll tell you how to do it, you just…. you know….. do it.”

That’s essentially what gets said to us on a daily basis here. It’s funny because when things are running smooth and waves are calm, our team can talk with our boss as a group and toss around the words Restructure and Reorganize all day long, and the boss is with us, and his boss is with us too. But like too many companies, we never execute those ideas. We go right back into maintenance mode for another few months until we toss around the R words all over again.

That is until a client freaks out and threatens to leave. Things always seem to pick up the pace at that point. Maintenance tasks are pushed to the side. Emergency meetings are held at regular intervals and that faint glimmer of hope that represents putting in place much needed infrastructure changes is now a fireball of possibility. Unfortunately, it fades just as fast it came.

You see, they don’t want the big scale changes you’ve been dreaming about. They don’t want to adopt the newest technologies. They don’t care about optimization. All they care about is healing the wound, placing a band-aid on the sore and giving the client immediate resolution. So you wind up implementing a 5th of what you wanted to do, or maybe even less. You end up creating several more strands of spaghetti to pile on top of the pile of you already have that would take years to unravel.

And once the client is appeased……… you move back into maintenace mode. Only now, you have that much more to maintain. And that glimmer of hope seems that much more dim.

When does it end?

Tip of the Day : SQL Multiple rows to Comma Separated Column

When working with SQL server and normalized data structures, there are often times when you need to flatten out the data for reporting or display purposes. There are probably several ways to do this, both in SQL server and in the presentation layer, but this is one of the methods I’ve been using, when it seems fitting to do so.

Admittedly this is probably a hack since I doubt this is how these commands were intended to be used, so if anyone has some alternative solutions, please let me know.

In addition, this example creates a table variable and manually plugs in the data. Ideally you would be running this on a properly normalized table structure using joins and etc. So please keep that in mind.

 

Declare @Aliases Table
(
	[FirstName] varchar(50) not null,
	[LastName] varchar(50) not null,
	[Alias] varchar(100) not null
)

Insert @Aliases
Select 'Clark','Kent','Superman'
Union All
Select 'Clark','Kent','Kal-El'
Union All
Select 'Clark','Kent','Gangbuster'
Union All
Select 'Clark','Kent','Supernova'
Union All
Select 'Clark','Kent','Nightwing'
Union All
Select 'Peter','Parker','Spiderman'
Union all
Select 'Peter','Parker','WebSligner'

Select Distinct
	[LastName],
	[FirstName],
	(Stuff((Select ', ' + Alias From @Aliases T2 Where T2.FirstName = T1.FirstName and T2.LastName = T2.LastName FOR XML PATH('')),1,2,'')) as Aliases
From @Aliases T1
Order By [LastName], [FirstName]

The FOR XML PATH(”) command after our select basically does the dirty work of getting the data merged into a single string. The stuff command then takes over and replaces the first comma with a blank. If you’ve never used the stuff command, takes 4 parameters. An input string, a starting index, a length, and a replacement string. It then replaces a subgstring of the input string (using the starting index and length as it’s stop and end points), with the replacement string.

Update: Sorry for the poor formatting, I need to figure out a better way to post code samples using this WordPress Template.

My time is my time and your time is my time.

We’ve been having some heated discussions at work lately based around being asked to work over, on a project that is not a necessity, with little to no notice. This happens much, much less than it used to, but it still does happen on occasion.

One of the guys sent this cartoon and I thought it summed up my outlook on the matter pretty well.
http://QwikLnk.com/ql59c6

Tip of the Day : SQL Union vs Union All

This is another one that I revisist every so often with some of the guys at work. If you have data in two different tables that you need to select back in one query, chances are you’ve come across the Union statement. But did you know that the Union statement, by itself, forces a Select Distinct when returning values? Now in some cases you may want to exclude duplicates, in others you may not. Union All, however, will not force a Select Distinct.

For an added performance boost to your queries and stored procedures, use the Union All statement if you know that you want to return back everything from your merged tables, regardless if there are duplicates or not.

In the example below, notice John Johnson is in both tables @Team1 and @Team2, however his name is only returned once using the Union and twice using the Union All.

Declare @Team1 Table(
	[ID] int identity(1,1) primary key,
	[FirstName] varchar(50),
	[LastName] varchar(50)
)

Declare @Team2 Table(
	[ID] int identity(1,1) primary key,
	[FirstName] varchar(50),
	[LastName] varchar(50)
)

Insert Into @Team1 values ('John','Johnson' 
Insert Into @Team1 values ('Jim','Johnson' 
Insert Into @Team1 values ('Joe','Smith' 
Insert Into @Team1 values ('Jacob','Clark'  

Insert Into @Team2 values ('Mark','Davis' 
Insert Into @Team2 values ('Albert','Einstien' 
Insert Into @Team2 values ('Brian','Thompson' 
Insert Into @Team2 values ('John','Johnson'  

Select FirstName, LastName
From @Team1
Union
Select FirstName, LastName
From @Team2

Select FirstName, LastName
From @Team1
Union All
Select FirstName, LastName
From @Team2

Tip of the Day : SQL Table Variables

Many times you need to create and use a table temporarily in SQL query or Stored Procedure and then destroy it when you’re done. A lot of times I see people using Temporary Tables to do this, but I’ve always opted for the table variable approach. For a quick run down on some of the pros and cons of Table Variables, check out this link.

To create a table variable, you do not write a Create Table statement. Instead, you use the Declare keyword to the define the variable the same as any other variable in SQL. However, when you define its type, you set it to table and then specify your colum definitions.

Declare @Test Table(
     [ID] int identity(1,1) primary key,
     [Name] varchar(50)
)

Insert Into @Test values ('John' 
Insert Into @Test values ('Jim' 
Insert Into @Test values ('Joe' 
Insert Into @Test values ('Jacob'  

Select *
From @Test

Tip of the Day : SQL Update from Joined Table

Updating a table with values from a Joined table.

I run into this question from the guys at work quite a bit, but it’s actually a pretty easy concept once you get the hang of it. If you have two tables that you want to sync values on, and you have a primary/foreign key you can join on, you’re all set.

Update Table1
Set Table1.ThisNeedsUpdated = Table2.WithThisNewValue
From Table1
Inner Join Table2 on Table1.ID = Table2.ID

If you get stuck, just remember a few things.

• The table in your “Update” caluse should be the same table in your “From” clause.
• You can join additional tables, nested selects or common table expressions as well and update your main table with values from those as well.
• If you’re working with two tables that have similiar field names, it’s best to fully qualify those fields in your statement to make it more readable.

Please comment if you have any questions or can’t get this working. I’d be glad to help.

ASP.NET Custom Server Controls - Custom Collection

I’ve been battling with trying to find a best practice method for developing a custom control in ASP.NET that has nested child controls. Specifically, I want something that functions much like the Wizard Control. I started out with an control that exposed an ITemplate property and used the InstantiateIn method, but this was all done using User Controls and just felt wrong. I started digging around some more and found this link http://QwikLnk.com/qla84e. Aha! Now it’s starting to make a bit of sense.

The utlimate goal is to (again) have something akin to the Wizard Control. What I mean is this:

Nevermind the name of these children, just focus on the concept. If you implement this with ITemplate, it because rather difficult to do what I wanted to do. I kept running into the fact that ITemplate lets you place ANYTHING inside the “Child” tags and I didn’t want that. I eventually found a way to eliminate this, but again, it just felt wrong.

The link above provided a little more insight into how this is supposed to function. Combined with the invaluable knowledge I found on the web that the “WizardSteps” tag I’m trying to emulate is actually a WizardStepCollection property of the Wizard control, and suddenly I feel like I’m heading down the right path.

Anyone have any experience with this?

The Server has Arrived

I ordered my first, honest to god, server earlier this week and took shipment of it yesterday. I finally came to the realization that if I wanted to make any reasonable amount of supplemental income, I needed to take my personal projects more seriously. Getting them hosted on an actual server and not some shared hosting web-farm, was the first step in doing that. It’s nothing that would even wet most geeks appetite as it’s extremely modest in it’s specs, but I plan on upgrading it as much as I can before shopping it over to WaveForm for hosting.

• Tyan Thunder K8S Pro Mainboard.
• Dual Opteron 2.0 GHZ Processors
• 4 GB (2 x 2) of Registered 333 MHZ DDR
• Dual Gigabit Ethernet and 1 10/100 Ethernet
• Single 120GB EIDE HDD

My plans for upgrading this before I send it off are as follows.

• Adding an additional 4GB (2 x 2) of DDR
• Installing 2 Western Digital Sata-150 80GB drives in Raid.

This should give me more than enough space to house the Databases and Source files on the raid drives and use the main drive partitioned for the OS and the other partition for a poor man’s backup solution. I’ll still be looking into ways to do an off site backup as well, but that may come later.

 

Update : I probably should have mentioned that I bought this via Geeks.com. First time I’ve ordered anything from them. The top plate was a little bent, but everything was intact and it runs like  a well oiled machine. It was also insanely cheap. Not too shabby.