CREATE PROCEDURE My.SP_ConvertToDynamicSQL
(
	@InClause VARCHAR(200) = NULL,
        @DateFrom DATETIME = NULL,
        @DateTo   DATETIME = NULL
)
AS

SELECT @SQL = @SQL + 'SELECT * FROM [X]'
SELECT @SQL = @SQL + ' WHERE [X].[X1] = 1'
IF (@DateFrom IS NOT NULL)
BEGIN
     SELECT @SQL = @SQL + ' AND [ColumnA] IN (' + @InClause+ ')'
END
IF (@DateFrom IS NOT NULL)
BEGIN
     SELECT @SQL = @SQL + ' AND [ColumnB] BETWEEN ''' + CONVERT(char(10), @DateFrom, 101) + ''' AND ''' + CONVERT(char(10), @DateTo, 101) + ''''
END

EXEC(@SQL)

RETURN

quote:

---

Originally posted by von

Jay,
...
I could always do a bunch of "AND MyDate BETWEEN COALESCE(@DateFrom, MyDate) AND COALESCE(@DateTo, MyDate)" or ISNULL(..) in my statements but is this always the most efficient way to do things?

In a SELECT statement with say 20-30 where conditions I'd say the COALESCE and ISNULL functions would be less efficient than narrowing the 20-30 where conditions down to say 3-4 by using the "IF [@Parm] IS NOT NULL..." which is why I'm leaning more towards dynamic sql. Also, I have a lot of values being passed in formated to be used in IN clauses.
...

---

quote:

---

Originally posted by Page47
I recommend you do what I always do when I am suspect of performance: write it a couple ways, test and determine what is best. I'd be interested to read your findings.

---

CREATE PROCEDURE SP_ConvertToDynamicSQL
(
        @DateFrom DATETIME = NULL
)
AS
	SELECT [DateFrom] = @DateFrom
GO

-- Simulate user's data entry capture from a web form 
-- and SQL generated dynamically in the application, say
DECLARE	@DateFrom	varchar(8000),
	@strSQL		varchar(8000)

SET @DateFrom = '20060101'';SELECT [name] FROM syobjects WHERE type=''U''--'

SET @strSQL = 'EXEC SP_ConvertToDynamicSQL @DateFrom = ''' + @DateFrom + ''''

SELECT [strSQL] = @strSQL

EXEC (@strSQL)
GO
DROP PROCEDURE SP_ConvertToDynamicSQL