Dynamically generate where condition

If we have a pre-defined SQL statement without "where conditions", how do I dynamically insert the where conditions into it (by user input or whatever)? Is there any free tool available for carrying out this kind of work?

Regards,

Ricky.

Ricky,

If the Where condition must be added to a Sql statement say within a Stored Procedure,

then you could use dynamic SQL statements.

declare @.sql varchar(255),

Select @.sql ="Predefined SQL statement" + "The where condition which can be input to the SP"

Exec @.sql

Sandoty

|||

Be cautious when you use the dynamic sql queries. It may cause two preaches,

1. SQL Injection

2. Permission issues

SQL Injection,

Your code is allowing the end user to inject the SQL command on your own code. So they can vulnerable your database be cautious here. Try to eliminate the comments (-- or /**/) as your input. You can control it on the UI itself. There are lots more practices are there. Google it for SQL Injection. You will get better idea.

Permission Issues,

Dynamic sql will expect the required permission on the source tables, if current user doesn’t have the permission on the table level (but on the SP), the dynamic SQL won’t work.

You can achieve the filter conditions from the SPs or prepared query itself. If the intention is reducing the code & time to achieve this task Pls take care the above issues.

|||

One more point about dynamic queries is the performance issues.

Each time you want to run them the sql command has to be recompiled in order to get an execution plan.

This may be positive in some situations. But most of the time the best way is to set your application to run with specific queries.|||

You can find a lot of information about this theme in the following articles.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB