question

pradeepmgs avatar image
pradeepmgs asked

Advance Search Query

tbl_Pro_Master proid proname proman tbl_plat_form paltform_id platformname tbl_pro_plat_form pro_palt_id(int) paltform_id(int) proid(int) Im trying to write advance search query based on selection my query is below..., alter proc proc_advance_search @Search varchar(1000)=null as begin select ppl.proplatformmasterid, pm.ProjectName as ProjectId, ppm.platformname as platformId from tblprojectplatformmaster ppl join tblProjectMaster pm on ppl.ProjectID=pm.ProjectID join tblplatformmaster ppm on ppl.platformid=ppm.platformid where ppl.IsProjectActive='Y' AND (@Search is null or pm.ProjectId like '%' + @Search + '%' or ppm.platformId like '%'+ @Search + '%') ENd it's not working please help me out...,
search
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
To do a query like this, you'll need to make it ad hoc. Then you can use [sp_execute_sql][1] to build your execution string. Just make sure you use the parameter values carefully or you could be looking at a SQL injection problem. [1]: http://msdn.microsoft.com/en-us/library/ms188001.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
When you say not working do you mean erroring, or not giving you the expected results? One issue is that your @Search parameter is a varchar(1000) whereas you are comparing it to either ProjectID or PlatformID, both of which are int. Depending on what you are trying to do here, Full Text Searching might be an area you want to investigate - this can give faster performance when searching for text in verbose fields.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.