Thanks a lot, but actually I send the parameter from the c# code without the ' ' characters, I put the

set @representative= 'Victaulic' as an example the actual sp is:

exec SP_SearchByCriteria2 null,null,'BAY ASSOCIATES, INC.',''

alter proc SP_SearchByCriteria

@catid int,

@manuid int,

@representative varchar(255),

@sort VARCHAR(255)

as

if(@catid is not null and @manuid is not null and @representative !='')

begin

exec ('

select me.Member_Id, UPPER(me.Member_Name)as Member_Name, me.Member_City, me.Member_State, me.Member_Fax

from members me, Manufacturers m, Manufacturers_Categories mc, Categories c, Representations r

where me.Member_Id = r.Member_Id

and m.Manufacturer_Id = r.Manufacturer_Id

and m.Manufacturer_Id = mc.Manufacturer_Id

AND c.Category_Id = mc.Category_Id

and c.Category_Id =' + @catid+

' and m.Manufacturer_Id=' + @manuid +

' and me.Member_Name = '+@representative +

' group by me.Member_Id, me.Member_Name, me.Member_City, me.Member_State, me.Member_Fax

order by me.'+@sort )

end

if(@catid is null and @manuid is null and @representative !='')

begin

exec ('

select me.Member_Id, UPPER(me.Member_Name)as Member_Name, me.Member_City, me.Member_State, me.Member_Fax

from members me, Manufacturers m, Manufacturers_Categories mc, Categories c, Representations r

where me.Member_Id = r.Member_Id

and m.Manufacturer_Id = r.Manufacturer_Id

and m.Manufacturer_Id = mc.Manufacturer_Id

AND c.Category_Id = mc.Category_Id

and me.Member_Name = char(39)'+ @representative + '+char(39)

group by me.Member_Id, me.Member_Name, me.Member_City, me.Member_State, me.Member_Fax

order by me.'+@sort )

end