hi,this is a sample based on adventureworks database:use adventureworks;godeclare @firstname nvarchar(50)declare @lastname nvarchar(50)declare @phone nvarchar(25)declare @ID nvarchar(20)--set @ID = 31--set @lastname = 'Al'--set @phone = '47'select contactID ,firstname,lastname,emailaddress,phone from Person.Contactwhere -- contactid like isnull(@ID, '%')and firstname like isnull(@firstname, '%')and lastname like isnull(@lastname, '%')and phone like isnull(@phone,'%')
I have four parameters (@ID, @firstname, @lastname, @phone) and i have have either one (random) parameter set or two or all or non. in each case i want to have all corresponding rows returned. for example if i set parameter @lastname to 'Alv' i want to have returned results same as with query:select * from person.contact where lastname like 'alv%'
same goes for all the other parameters.only @ID must have exact match -without like operater.is there a better practise to do this.thank you
multi paramaters agains one table
Posted - 2011-04-07 : 12:13:20
...Although, I should add that in the grand scheme of things, too many (null or satisified) conditions tend to make it difficult for the query to store a decent (appropriate) query plan for the various combinations of significant parameter values.For example, if people generally search using the @id param... and search by @lastName will not be as fast as it could be because the cached plan had @id in mind...not sure what the links say... but maybe they mention this -^Corey