SQL: Conditional WHERE clause
I did something for the first time today – A conditional where clause, based on a parameter passed or a condition set earlier in the procedure. Its not exactly the first time I’ve done this, but I use to do it very differently.
In the past I would have built the SQL up in a string and then execute it:
CREATE PROCEDURE MyProc @Param1 VARCHAR(100) AS
DECLARE @SQL VARCHAR(8000)
SET @SQL = ‘
SELECT
Field1,
Field2,
Field3
FROM Table1
WHERE ‘
IF @Param1 = ‘A’
BEGIN
SET @SQL = @SQL + ‘ Field5 = 55 ‘
END
ELSE
BEGIN
SET @SQL = @SQL + ‘ Field7 < 10 ‘
END
EXEC (@SQL)
Currently I work with a DBA who is a purist and would not allow the above code in his database – which is excellent by the way! It means that code that gets implemented is a high standard.
So, I ended up writing the procedure as follow:
CREATE PROCEDURE MyProc @Param1 VARCHAR(100) AS
SELECT
Field1,
Field2,
Field3
FROM Table1
WHERE ((@Param1 = ‘A’ AND Field5 = 55)
OR (Field7 < 10))
It just proves again actually how good it is to have a DBA and a proper walkthrough process in place. Previously I would not have been bothered I suppose, and just implemented the 1st block of code, but now every line of SQL I write I have a little DBA on my shoulder telling me: If you don’t change that – he is going to send it back and you gonna have to redo it in any case!