Alternative to Dynamic SQL?

Question

Hello,

I've built a dynamic sql for stored proc and pulled results in to a Temp Table to be rendered on to a Report. However, my lead is against dynamic sql usage. Please suggest an alternative to NOT using dynamic query.

--Stored Params include ID, FromYear, ToYear
DECLARE @sqlQuery1 VARCHAR(4000), @sqlQuery2 VARCHAR(255)=NULL, @sqlQuery3 VARCHAR(255)=NULL, @sqlResult VARCHAR(MAX)
                , @FromYear INT=NULL, @ToYear INT=NULL

SET @sqlQuery1 = 'SELECT CompanyCode, ModelID, ModelDescription, Quantity FROM CustomerProduct WHERE ID=123'

SET @sqlQuery2 =  CASE WHEN @FromYear IS NOT NULL THEN ' AND FromYear = '+ @FromYear + '' ELSE '' END
SET @sqlQuery3 =  CASE WHEN @ToYear IS NOT NULL THEN ' AND ToYear = '+ @ToYear +'' ELSE '' END

SET @sqlResult = @sqlQuery1 + @sqlQuery2 + @sqlQuery3

print @sqlResult

Also, the above query result appends 00 at the end. Please assist.

Answer 1

Seems like this would work.

SELECT CompanyCode, ModelID, ModelDescription, Quantity FROM CustomerProduct WHERE ID = 123
                AND (@FromYear IS NULL OR FromYear = @FromYear)
                AND (@ToYear IS NULL OR ToYear = @ToYear)

Or perhaps this, but profile to see which performs better.

SELECT CompanyCode, ModelID, ModelDescription, Quantity FROM CustomerProduct WHERE ID = 123
                AND FromYear = ISNULL(@FromYear, FromYear) 
                AND ToYear = ISNULL(@ToYear, ToYear)

Answer 2

Try this:

SELECT CompanyCode, ModelID, ModelDescription, Quantity 
FROM CustomerProduct 
WHERE ID = @ID
AND (1 = 1 AND @FromYear IS NOT NULL AND FromYear = @FromYear)
AND (1 = 1 AND @ToYear IS NOT NULL AND ToYear = @ToYear);