All posts by philipsymons

Sql Tricks for Tricky Logic (3)

This is the last posting in my brief discussion of SQL tricks. I’m dealing here with an issue which causes quite a bit of confusion to newer developers. What’s the point in having LEFT and RIGHT joins defined in the SQL standard, how do you decide which to use, and is there a way to keep things simple? Continue reading Sql Tricks for Tricky Logic (3)

SQL Tricks for Tricky Logic (2)

I’ve been working with Linq in .NET lately and there have been a few posts I would have liked to add but since I said I was going to blog SQL tricks I’m putting other topics to one side for the moment.

So… another situation where it would be useful to have an SQL trick is the case of the filtered outer join. Continue reading SQL Tricks for Tricky Logic (2)

SQL Tricks for Tricky Logic

One reason occasionally offered for using dynamic SQL widely, or even for keeping business logic out of database stored procedures, is the difficulty of coding optional filters in SQL. Sometimes we may want a row from table X where column A = this, column B = this and column C = this. Other times we may want all the rows where column A = this but we’ll accept any value in columns B and C. Continue reading SQL Tricks for Tricky Logic

Using the User-defined Table types with ADO.Net structured parameter type

Late in March I released (started using a lot) a small C#/SQL Server project. It utilises the new User-Defined Table Type in SQL Server 2008 and the “structured” parameter type in ADO.NET. This allows a table originated on the client to be passed in to a stored procedure as a parameter. Oracle has had table data types for a while of course, and there are still some limitations to the SQL Server version, but it’s certainly very easy to use. Continue reading Using the User-defined Table types with ADO.Net structured parameter type