May 25, 2010 at 2:23 am
sorry , i have asyntax error near ')'
would you please help me?
Select Emp_Name,Emp_Surename,Emp_Shomareshenasname,Edarekol_Id,Vahed_Id
from Employee
where
(Emp_Name=Case when @VarEmp_Name is not null Then @VarEmp_Name Else Emp_Name)And
(Emp_Surename=Case when @VarEmp_Surename is not null Then @VarEmp_Surename Else Emp_Surename)and
(Emp_Shomareshenasname=case when @VarEmp_Shomareshenasname is not null then @VarEmp_Shomareshenasname else Emp_Shomareshenasname)and
(Edarekol_Id=case when @VarEdarekol_Id is not null then @VarEdarekol_Id else Edarekol_Id)and
(Vahed_Id= case when @VarVahed_Id is not null then @VarVahed_Id else Vahed_Id)
End
May 25, 2010 at 2:33 am
The problem was a missing "(" and "end"
Rectified code:
Select Emp_Name,Emp_Surename,Emp_Shomareshenasname,Edarekol_Id,Vahed_Id
from Employee
where
(Emp_Name=Case when 'a' is not null Then 'a' Else Emp_Name end)
And
(Emp_Surename=Case when 'b' is not null Then 'a' Else Emp_Surename end)
and
(Emp_Shomareshenasname = case when '' is not null then '' else Emp_Shomareshenasname end)
and
(Edarekol_Id=case when '' is not null then '' else Edarekol_Id end)
and
(Vahed_Id= case when '' is not null then '' else Vahed_Id end)
Hope this helps you!
May 25, 2010 at 2:33 am
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://www.sommarskog.se/dyn-search.html
Select Emp_Name, Emp_Surename, Emp_Shomareshenasname, Edarekol_Id, Vahed_Id
from Employee
where (Emp_Name = Case when @VarEmp_Name is not null Then @VarEmp_Name Else Emp_Name end)
And (Emp_Surename = Case when @VarEmp_Surename is not null Then @VarEmp_Surename Else Emp_Surename end)
and (Emp_Shomareshenasname = case when @VarEmp_Shomareshenasname is not null then @VarEmp_Shomareshenasname else Emp_Shomareshenasname end)
and (Edarekol_Id = case when @VarEdarekol_Id is not null then @VarEdarekol_Id else Edarekol_Id end)
and (Vahed_Id = case when @VarVahed_Id is not null then @VarVahed_Id else Vahed_Id end);
You needed an END to terminate each CASE statement. Read the links above.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 25, 2010 at 2:35 am
Rectified the code again with the Vairables yoyu used!
Code:
Select Emp_Name,Emp_Surename,Emp_Shomareshenasname,Edarekol_Id,Vahed_Id
from Employee
where
(Emp_Name=Case when @VarEmp_Name is not null Then @VarEmp_Name Else Emp_Name end)
And
(Emp_Surename=Case when @VarEmp_Surename is not null Then @VarEmp_Surename Else Emp_Surename end)
and
(Emp_Shomareshenasname=case when @VarEmp_Shomareshenasname is not null then @VarEmp_Shomareshenasname else Emp_Shomareshenasname end)
and
(Edarekol_Id=case when @VarEdarekol_Id is not null then @VarEdarekol_Id else Edarekol_Id end)
and
(Vahed_Id= case when @VarVahed_Id is not null then @VarVahed_Id else Vahed_Id end)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply