T-SQL stored proc conditionals in where clause

Question

I am trying to write a stored procedure in which the where clause needs to be dynamic based on the parameter passed to the procedure.

Based on whether an empty string is passed to StartDate and EndDate, the ContractDate condition selects rows. If they are empty strings, the user would have passed a list of dates(assumption) and I need to select rows that have ContractDates sames as the ones in the list. If the @StartDate and @EndDate params are not empty strings, I select rows that have ContractDate >= @StartDate and <=@EndDate. How will I incorporate this in my proc? The current code results in a syntax error.

select TermDescription,ContractDate,Price,SortOrder into #tbtp from BaseTermPrice 
inner hash join Term 
on 
Term.TermID = BaseTermPrice.TermID
where 
BaseID = @BaseID and PeakType = @PeakType and 
case when @StartDate != '' and @EndDate != '' 
then  
ContractDate >= @StartDate and ContractDate <= @EndDate
else
ContractDate in (@DateList)
end
order by 
ContractDate,SortOrder

"`string is passed to StartDate and EndDate`" Why are you using strings for fields named StartDate and EndDate? That's an awful way to work with date information. — Joel Coehoorn, Apr 17 '14 at 20:12
Yes. Ideally I would like to pass nulls or actual dates. How do I equate parameters to null in SQL. I am used to programming languages where equating to nulls is easy. I am relatively new to SQL and cant figure out how you equate something to null? — Sai, Apr 17 '14 at 20:15
That depends on what client language and platform you're using. — Joel Coehoorn, Apr 17 '14 at 20:18
I am using C# as the client language and it is quite easy to pass nulls. But when calling a stored proc should I pass DBNull or just null? How do I verify in T-SQL for null value? — Sai, Apr 17 '14 at 20:21

score 0 · Answer 1 · answered Apr 17 '14 at 20:11

0

You should look into Dynamic SQL. Best place to start is http://www.sommarskog.se/dynamic_sql.html. Dynamic SQL lets you build the SQL string programmatically based on whatever conditions you need.

answered Apr 17 '14 at 20:11

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

You can also accomplish it without dynamic SQL. I prefer not to use it to avoid syntax errors which would only be found at run time.

You would need to restructure your procedure to just be IF blocks which contain full SQL statements, not fragments.

For the @DateList variable:

Parse the @DateList variable into a temp table, #DateListTable. See top answer here: How Do I Split a Delimited String in SQL Server Without Creating a Function?
Change relevant SQL to ContractDate in (Select ContractDate from #DateListTable)

edited May 23 '17 at 11:49

Community

1
1

answered Apr 17 '14 at 20:16

toddsonofodin

513
2
4

1

That would work fine for this question with it's limited number of variables. Dynamic SQL gets more important when you have a lot of conditionals to check - the number of strings you need to write increases exponentially. – Apr 17 '14 at 20:24

score 0 · Accepted Answer · answered Apr 17 '14 at 20:43

Thanks for all your valuable suggestions. Just ended up cracking this with ands and ors

select TermDescription,ContractDate,Price,SortOrder into #tbtp from BaseTermPrice 
inner hash join Term 
on 
Term.TermID = BaseTermPrice.TermID
where 
BaseID = @BaseID and ((@PeakType IS NULL and PeakType is null) or (@PeakType IS NOT NULL and PeakType=@PeakType))
and ((@DateList IS NULL and ContractDate between @StartDate and @EndDate) or (@StartDate IS NULL and ContractDate in (@DateList)))
order by
ContractDate,SortOrder

T-SQL stored proc conditionals in where clause

3 Answers3