WHERE MyColumn1 >= @MyColumn1_START
AND MyColumn1 <= @MyColumn1_END
AND MyColumn2 LIKE '%' + @MyColumn2 + '%'

WHERE (@MyColumn1_START IS NULL OR MyColumn1 >= @MyColumn1_START)
AND (@MyColumn1_END IS NULL OR MyColumn1 <= @MyColumn1_END)
AND (@MyColumn2 IS NULL OR MyColumn2 LIKE '%' + @MyColumn2 + '%')

SELECT @strWHERE = ... string concatenation ...
FROM MyDefinitionTable
WHERE (ColumnName = 'MyColumn1_START' AND @MyColumn1_START IS NOT NULL)
OR (ColumnName = 'MyColumn1_END' AND @MyColumn1_END IS NOT NULL)
OR (ColumnName = 'MyColumn2' AND @MyColumn2 IS NOT NULL)

(@MyColumn1_START IS NULL OR MyColumn1 >= @MyColumn1_START)
...

SELECT @MyUsedColumns = 
       CASE WHEN @MyColumn1_START IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN @MyColumn1_END IS NOT NULL THEN 2 ELSE 0 END
     + CASE WHEN @MyColumn2 IS NOT NULL THEN 4 ELSE 0 END

WHERE (@MyColumn1_START IS NULL OR MyColumn1 >= @MyColumn1_START)
AND (@MyColumn1_END IS NULL OR MyColumn1 <= @MyColumn1_END)
AND (@MyColumn2 IS NULL OR MyColumn2 LIKE '%' + @MyColumn2 + '%')
AND 7 <> -1

quote:

---

which should generate a WHERE clause along the lines of:

WHERE MyColumn1 >= @MyColumn1_START
AND MyColumn1 <= @MyColumn1_END
AND MyColumn2 LIKE '%' + @MyColumn2 + '%'

So I want to mechanically generate the SQL string for that WHERE clause. I can do that easily (and generically) from the table which defines the columns / conditions.

---

quote:

---

Originally posted by gbritton

You have a two big problems:

1. You can't anticipate how your query will be used

---

quote:

---

2. You can't code for all contingencies.

---

quote:

---

Surely you monitor production and fix incorrect or poorly performing queries?

---

quote:

---

Originally posted by ScottPletcher

Hmm, so you know the names of the local variables that will be used? If so, you can actually generate code to dynamically read those values, which you could then test as you were dynamically constructing the SQL string.

---

SELECT @strWHERE = ... string concatenation ...
FROM MyDefinitionTable
WHERE (ColumnName = 'MyColumn1_START' AND @MyColumn1_START IS NOT NULL)
OR (ColumnName = 'MyColumn1_END' AND @MyColumn1_END IS NOT NULL)
OR (ColumnName = 'MyColumn2' AND @MyColumn2 IS NOT NULL)

quote:

---

Originally posted by gbritton

There's an old saying, "In theory, theory is the same as practice, but in practice, it isn't"

---

quote:

---

If you "know that if I do nothing the query will be cached and use the wrong index for alternative scenarios" then develop custom query plans for the ones you DO know about before you promote.

---

SELECT @strSQL = @strSQL + ' AND ' + MyColumnName + ' = @' + MyColumnName
FROM MyDefinitionTable
WHERE SomePrimaryKey = @MyID

SELECT @strSQL = @strSQL + FunctionSQLSnippet
FROM dbo.MyFunction(@MyID)
WHERE (FunctionColumnName = 'MyColumn1_START' AND @MyColumn1_START IS NOT NULL)
OR (FunctionColumnName = 'MyColumn1_END' AND @MyColumn1_END IS NOT NULL)
OR (FunctionColumnName = 'MyColumn2' AND @MyColumn2 IS NOT NULL)

CREATE FUNCTION dbo.MyFunction
(
	@DefinitionID	int	-- ID of the definition to use in MyDefinitionTable
)
RETURNS @tblArray TABLE
(
	S_ID			int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	FunctionSQLSnippet	varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	FunctionColumnName	varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
AS
BEGIN

	INSERT @tblArray(FunctionSQLSnippet)
-- NOTE: FunctionSQLSnippet will actually use some sort of "Action" column to generate 
--           StartsFrom, EndsAt, Range, Contains, Equals and other variations of SQL code!
	SELECT	[FunctionSQLSnippet] = ' AND ' + MyColumnName + ' = @' + MyColumnName,
		[FunctionColumnName] = MyColumnName
	FROM	dbo.MyDefinitionTable
	WHERE	SomePrimaryKey = @DefinitionID
	RETURN
GO

quote:

---

Originally posted by gbritton

I suppose the parameters would be passed to the master?

---