Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arielspecial
Starting Member
11 Posts |
 Posted - 2011-10-03 : 04:04:44
|
| I couldn't find the right question to find this:I have an sp which gets params: a few of the params are search terms like: @email, @fromDate, @toDate. I want to check if, for example @email is not empty, and if not add to the 'where' part the query:where @email like '%aaa@aaa.net%' and if its empty do nothing.I tried using IF (len(@email)=0) then ... but it didn't really work. is this the right way or is there another better approach?(also tried using exec(sql_query) but with complex queries I had many errors and it seemed unreadable). |
|
|
arielspecial
Starting Member
11 Posts |
Posted - 2011-10-03 : 04:24:59
|
| I realized I never tried searching for : '"sql server" "dynamic where"' and I found this:http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=469use pubsdeclare @LastName as nvarchar(100)select @LastName = 'White'select au_id,au_fname,au_lnamefrom Authorswhere au_lname = casewhen DataLength(@LastName) > 0 then @LastNameelse au_lnameendbut still there is a problem here with au_lname like '%@LastName%' or is it not a problem? |
 |
|
|
vmvadivel
Yak Posting Veteran
69 Posts |
Posted - 2011-10-03 : 04:31:56
|
| Make use of COALESCE. Something like this:WHEREEmailID LIKE '%' + COALESCE(@email, EmailID) + '%'Best RegardsVadivelhttp://vadivel.blogspot.com |
|
|
|
arielspecial
Starting Member
11 Posts |
Posted - 2011-10-03 : 05:48:51
|
| OK, I create a query string and concatenate what I need using 'if' and than I exec(@queryStr) and its working fine.declare @queryStr nvarchar(max)declare @email nvarchar(50)set @queryStr = 'select * from tbl where pid=1 'if (DataLength(@email)>0) select @queryStr = @queryStr + ' and email like '%' + @email '%'' ' exec(@queryStr)Thanks |
|
|
|
vmvadivel
Yak Posting Veteran
69 Posts |
Posted - 2011-10-03 : 08:23:34
|
| You don't need a dynamic query for this requirement. You can avoid it by using the sample shown in my previous post.Best RegardsVadivelhttp://vadivel.blogspot.com |
|
|
|
arielspecial
Starting Member
11 Posts |
Posted - 2011-10-03 : 08:51:06
|
| Vadivel, thank you for your reply.as I looked into COALESCE, I read that:COALESCE(expression1,...n) is equivalent to the following CASE expression:CASEWHEN (expression1 IS NOT NULL) THEN expression1WHEN (expression2 IS NOT NULL) THEN expression2...ELSE expressionNEND My problem is not to check if @email is null or empty, and than do something, but to actually control the format of the Where clause, like: if @email is empty do x, if it has digits in it do Y, and if not do Z in the Where clause.I don't know, it's not clear enough. |
|
|
|
arielspecial
Starting Member
11 Posts |
|
|
|
|
|
|
|
dynamic where..