andrewd.smith (8/13/2010)

---

"WHERE (1 = 1)" is sometimes convenient when generating dynamic SQL.

It avoids having to determine if a particular filter criterion in the WHERE clause is the first in the list, so all filter criteria can be prefixed with "AND". For example:

CREATE PROCEDURE MyDynamicQuery

@Param1 varchar(20) = NULL,

@Param2 varchar(50) = NULL,

@Param3 datetime = NULL

AS

BEGIN

DECLARE @sql nvarchar(1000)

DECLARE @paramList nvarchar(1000)

SELECT @paramList = N'@P1 varchar(20), @P2 varchar(50), @P3 datetime'

SELECT @sql = N'SELECT Col1, Col2, Col3 FROM dbo.MyTable WHERE (1=1)'

IF NOT (@Param1 IS NULL)

SELECT @sql = @sql + N' AND (Col1 = @P1)'

IF NOT (@Param2 IS NULL) BEGIN

SELECT @Param2 = @Param2 + '%'

SELECT @sql = @sql + N' AND (Col2 LIKE @P2)'

END

IF NOT (@Param3 IS NULL)

SELECT @sql = @sql + N' AND (Col3 >= @P3)'

--PRINT @sql

EXEC sp_executesql @sql, @paramList, @Param1, @Param2, @Param3

RETURN

END