Thanks for the response.

The cost on using functions on a where clause come when using the function on the column because it causes a table scan even on an indexed field. When the function is used...

Why are you creating constants when you can just use the date functions in the where clause?



SELECT sum(Salesamount)
FROM Table
WHERE salesdate BETWEEN ...

There are alot of ways to do this here are a couple examples.

Using Year() function

Select sum(Salesamount)
From Table
where Year(salesdate) = Year(@userInput)

You can't use the alias in the boolean test plus you should use a having clause to test the count(*) and not the where clause.


Select title, count(*) As Cnt
From poss_titles
Group By title...

Did you try using wild cards.


Select * from Table1
Where Name LIKE ('%' + @Name + '%')

I don't see where you are getting the alias 'c'

Besides that i think you are making it more difficult then it has to be.



select *
from dbo.tblLevelOneApprover a,...

Select Product From Table
Where Component in ('part1','part3')

What you need to do is build the query for only the selected options within the code block. If you build a static select query with all the options then it will return nothing because no user will...