SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Allowing * in SQL query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sparrow37
Posting Yak Master

145 Posts
Posted - 11/08/2012 :  07:57:32  Show Profile  Reply with Quote


I have a stored procedure and I want to allow * in it so that if user:

types t* then taller, tea returns
types * or . then all results are returned
types t then all results which have t are returned like ptv, tall, sit

my stored procedure is this:

ALTER PROCEDURE [dbo].[SearchEntityDataNew]
@SearchText varchar(100) = '*'

AS
BEGIN
SELECT 'Entity' as pagetype,EntityID,EntityData,EntityDataID
FROM EntityData
WHERE EntityData LIKE '%' + @SearchText+ '%'
OR @SearchText = '*'

union all

SELECT 'Property' as pagetype,PropertyID,PropertyValue,EntityDataID
FROM EntityDataProperty
WHERE PropertyValue LIKE '%' + @SearchText+ '%'
OR @SearchText = '*'
END

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/08/2012 :  08:35:43  Show Profile  Reply with Quote
Is that not working as you expect it to? Seems like the search condition can be 
WHERE EntityData LIKE
CASE 
     WHEN @SearchText NOT LIKE '%*%' THEN '%' + REPLACE(@SearchText , '*', '%') + '%'
     ELSE REPLACE(@SearchText , '*', '%')
END
Edited by - sunitabeck on 11/08/2012 08:36:29
Go to Top of Page

nathansneely
Starting Member

USA
2 Posts
Posted - 11/08/2012 :  09:13:36  Show Profile  Reply with Quote
Maybe something like this would work.. Although sunitabeck's response looks simpler..

ALTER PROCEDURE test.[SearchEntityDataNew]
@SearchText varchar(100)

AS
BEGIN
IF @SearchText = '*' OR @SearchText = '.'
BEGIN
SELECT myfield FROM mytable
END
IF RIGHT(@SearchText ,1) = '*'
BEGIN
SELECT myfield FROM mytable WHERE myfield LIKE LEFT(@SearchText, LEN(@SearchText) - 1) + '%'
END
IF RIGHT(@SearchText ,1) <> '*'
BEGIN
SELECT myfield FROM mytable WHERE myfield LIKE '%' + SearchText + '%'
END
END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000