CODEWITHSUNDEEP
c#sqlwhere-clauseparameterizedclause
user1368731
user1368731

Reputation: 1

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

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?

Upvotes: 0

Views: 1652

Answers (2)

Taryn
Taryn

Reputation: 247860

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

Upvotes: 0

Zachary
Zachary

Reputation: 6532

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.

Upvotes: 1

Related Questions