Hi friend,

I have written a query in oralce for searching ...for example in matrimonial site searching a a bride or groom based on seelct value...now i required to change the query in sql server...can anybody help...
the code i have written in oracle

procedure Get_viewOrders(reqno in varchar2,reqdate in varchar2,employeename in varchar2)
As
v_qry varchar2(10000);
begin
v_qry:='select tr.requestno,us.firstname||' ' ' '||,tr.status from travelorders tr,users us where lower(us.id)=lower(tr.id)'
if reqno is not null then
v_qry:=v_qry || 'and tr.requestno=upper(' ' ' || reqno || ' ' ')'
end if;
if requestdate is not null then
v_qry:=v_qry || 'and to_date(tr.requestdate,' 'dd\mm\yyyy' ')=to_date(' ' ' ||reqdate|| ' ' ',' ' dd/mm/yyyy' ')'
end if
end Get_viewOrders


thanks
shree

and i tried to write the query as shown below

ALTER procedure [dbo].[search_Improved](@firstname varchar(50)=null)
AS
BEGIN
declare @sql nvarchar(4000)
select @sql='select *from EmpDetails where 1=1'
if @firstname is not null
select @sql=@sql+' and fname=@firstname'
END

it returns 0... didnt find the solution