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 like yours. The short answer is NO. — Steve
– Steve, Commented May 1, 2012 at 21:59
Have you considered using EF or Linq-To-SQL to do this instead? — KingCronus
– KingCronus, Commented May 1, 2012 at 22:04

Zachary · Accepted Answer · 2012-05-01 22:00:24Z

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 1, 2012 at 22:00

Zachary

6,53224 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1368731 Over a year ago

We actually do optional parameters elsewhere in our code. This should work here as well. Thanks!

Taryn · Accepted Answer · 2012-05-01 22:02:01Z

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 1, 2012 at 22:02

Taryn

249k57 gold badges375 silver badges409 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related