Generic Search Stored Procedure

[iaresean]

Hi All;

Does anyone know of (or know how to make) a generic search stored procedure.

I really need a procedure that will allow me to pass it a table name along with a search string. Using the search string the stored procedure will search through any string type columns (varchar, nvarchar, text etc) and return any rows for which a match was found.

I would be SUPER SUPER appreciative if anyone could help me out on this one. I have been searching google high and low, but I haven't managed to find anything yet.

Thank you!

Sean.

 

[ca8msm]

If you run this:

select so.name, sc.name, st.name
from sys.objects so
INNER JOIN syscolumns sc ON so.object_id = sc.id
INNER JOIN sysTypes st ON sc.xType = st.xType
where so.type = 'u'

you should get a list of all your tables and fields. I guess you'd have to loop through this table and use dynamic sql to execute a search on each relevant field/table.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[iaresean]

Cool, thanks ca8msm.

I used some of your logic (fetching the columns), and managed to create my own procedure. Managed to learn some things in the process. Thanks for the guidance all the same.

CREATE PROCEDURE sp_SearchTable
(
    @TableName nvarchar(100),
    @SearchStr nvarchar(100)
)
AS
BEGIN

    DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SearchSQL nvarchar(2000);

    SET @SearchSQL = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    DECLARE Cursor_Columns CURSOR FOR 
        SELECT QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = PARSENAME(@TableName, 1)
        AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

    OPEN Cursor_Columns

    FETCH NEXT FROM Cursor_Columns 
        INTO @ColumnName

    WHILE @@FETCH_STATUS = 0 BEGIN
    
        IF @SearchSQL = ''  BEGIN
            SET @SearchSQL = @ColumnName + ' LIKE ' + @SearchStr2
        END
        ELSE BEGIN
            SET @SearchSQL = @SearchSQL + ' OR ' + @ColumnName + ' LIKE ' + @SearchStr2
        END

        FETCH NEXT FROM Cursor_Columns 
            INTO @ColumnName
    END

    CLOSE Cursor_Columns
    DEALLOCATE Cursor_Columns

    PRINT ('SELECT * FROM ' + @TableName + ' WHERE ' + @SearchSQL)
    
END

Hope someone else finds it useful. :-D

Regards;

Sean.

 

[iaresean]

Sorry, forgot to change the last command 'PRINT' to 'EXEC'. Please do so to get the stored proc to work.

Regards;

Sean.

 

[ca8msm]

You might want to look at doing this without the use of a cursor. Dynamic SQL and a cursor in an SP sounds like the performace will be awful!


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[iaresean]

:-(

Should I go for a temp table rather?

Sean.

 

[Chance1234]

Is this any help to you

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Chance,

Filmmaker, gentleman and polla stilo eleous

 

[iaresean]

Hi Chance;

Yes I did come across this, but this isn't exactly what I am looking for. My requirements are fairly different.

Sean.

 

[AlexCuse]

Maybe a look at this thread would be helpful?

thread183-1298424

You will just need to modify it to limit your search to one table's columns.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

Let me explain to you why a generic search is an extremely bad idea if you are planning to invoke it from your user interface:
1. Performance - the techniques to get this kind of search are performance hogs. While they might look ok on a dev machine with minimal test records, if your data base will be large with many users, this is a prescription for timeouts and annoyed users. Further with the dynamic approach it is virtually impossible to performance tune the query once the performance issues start. Then you have angry users with an application that doesn't work and which needs to be re-written from scratch to get rid of the search everywhere inteh application that it is used. This is what causes software projects to fail and people to lose their jobs and sometimes for the company to go out of business.
2. Debugging - This approach is impossible to adequately debug because you can't tell what will come in as the where clause. This virtually guarantees errors in execution when mistakes in making the where clause are exposed in use rather than in testing.
3. Security - google for SQL Injection Attack

It may seem simpler and faster to write one search proc that will be used everywhere. In all cases this is a bad idea. If this is your requirement, you need to present a case to them as to why it's a bad idea.


Questions about posting. See faq183-874

 

[iaresean]

Hi SQLSister,

Wow, I can see that you have had some bad experiences with this before. You can rest assured that this is not going to be for any high usage front end components.

It is merely to power a simple backend list, that I believe will have at most 2 people ever using it - there is even a good possibility that there will never be more than 1 person using it at a time! :-D

Also, I am using ASP.NETs enterprise manager to do parameterized stored procedures invokation. I was told this would help against SQL Injection. Your thoughts on this? On a side note though, they would have to first hack my login procedure to even get to that stage.

I can understand your concern, and believe me I am more than happy to hear it. I think it was a good idea for you to bring this up here, just in case someone did decide t implement this type of search on a large user base implementation.

You have my word that I will not implement my script on anything that is performance sensitive, or is public accessible.

Perhaps you can give directions to some tutorials discussing your favourite method(s) of implementing searches? It would be appreciated.

Regards;

Sean.