Dynamic queries

Hi,

This question has been asked probably million times, but it sems that I cannod find right answer on search engines.

I need to send parameters to my stored procedure, but not only parameters. For example in where clause I have something like:

where myID = 12

I need to be able to filter results on myID, but one time I need it to be eqal to 12 and other time I need it to be different (<>) from 12. Some time I even need to add another condition like myID2 = 1. Can I solve this without additional procedures?

I believe that I saw solution in3-Tier ArchitectureTutorial Series few weeks ago but it seems that something changed, and I cannot find it anymore. Can anyone help?

you should be able to do this without additional procedures,

why not build a sort of "query builder"

you would start with as much of the sql string as is commin in all queries so

string strSql = "select * from myTable where myID";

then depending on logic in your app (select Equals or not) you start building your query string

if(ddEqualsOrNot.SelectedValue == "equals"){
strsql+= "="; //adds on an equals
}else if(ddEqualsOrNot.SelectedValue == "not equal"){
strsql+= "!=" //adds not equals
}

then finish off the sql line
strSql += " @.myId;";

then you can build the same command or dataAdapter object and add @.myId as a parameter. depending on if they picked Equals or not equals your string would finally look like

"select * from myTable where myId = @.myId;"

or

select * from myTable where myId != @.myId;

hth,

mcm

|||

This is what first came in my mind, but I just prefer to use Stored procedures over sql in code behind files.

However, for your proposed solution I have one question are you sure that I should use "!="? Is it "<>"?

|||

Hi

<> is fine.

Also you can contruct sql script in stored procedure.

declare @.sql_statementnvarchar(1024)--initset @.sql_statement ='select * from ';--build sql here-- print sql and execprint @.sql_statementexecsp_executesql @.sql_statement