Building a WHERE clause programmatically, then passing it into SQL as a parameter

Question

On my asp.net project, there are instances where a server side filter function in C# is building an SQL WHERE clause, then passing it into an SQL stored procedure for example,

Filter produces a string variable with a value like “WHERE Qty < 5 AND Price > 10”.

The relevant part of the Stored Procedure is like:

Exec (‘
    Select Name, Location
    From Users
    + ‘@passedInWhereClause’
‘)

As you can see, we have to do an EXEC command on a string built with the passed in variable containing the where clause that was generated by C# code on the server side. I would really like a way to eliminate the EXEC and make the SQL code look more professional, instead of a giant string wrapped with an EXEC. Is there any better way to do this?

[This is another question](http://stackoverflow.com/questions/6945461/t-sql-dynamic-where-condition) like yours. The short answer is NO. — Steve, May 01 '12 at 21:59
Have you considered using EF or Linq-To-SQL to do this instead? — KingCronus, May 01 '12 at 22:04

score 1 · Answer 1 · answered May 01 '12 at 22:00

1

You should consider optional parameters, example

WHERE (@Type = NULL OR @Type = '' OR @Type = Type)

This allows you to pass a NULL or blank to the SP to ignore the where clause, or you pass a value to have the where clause applied.

answered May 01 '12 at 22:00

Zachary

6,522
22
34

We actually do optional parameters elsewhere in our code. This should work here as well. Thanks! – user1368731 May 02 '12 at 16:14

score 0 · Answer 2 · answered May 01 '12 at 22:02

0

No, there is not a better way to do this, as you are building Dynamic SQL to execute.

If you want to do it better, then don't run Dynamic SQL.

The Curse and Blessings of Dynamic SQL

answered May 01 '12 at 22:02

Taryn

242,637
56
362
405

Building a WHERE clause programmatically, then passing it into SQL as a parameter

2 Answers2