Dynamically search columns for given table

问题:

I need to create a search for a java app I'm building where users can search through a SQL database based on the table they're currently viewing and a search term they provide. At first I was going to do something simple like this:

SELECT * FROM <table name> WHERE CAST((SELECT COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table name>') 
  AS VARCHAR) LIKE '%<search term>%'

but that subquery returns more than one result, so then I tried to make a procedure to loop through all the columns in a given table and put any relevant fields in a results table, like this:

CREATE PROC sp_search
    @tblname VARCHAR(4000),
    @term VARCHAR(4000)
AS
    SET nocount on
    SELECT COLUMN_NAME 
    INTO #tempcolumns
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tblname

    ALTER TABLE #tempcolumns
    ADD printed BIT,
        num SMALLINT IDENTITY

    UPDATE #tempcolumns
        SET printed = 0

    DECLARE @colname VARCHAR(4000),
        @num SMALLINT
    WHILE EXISTS(SELECT MIN(num) FROM #tempcolumns WHERE printed = 0)
    BEGIN
        SELECT @num = MIN(num) 
            FROM #tempcolumns 
            WHERE printed = 0
        SELECT @colname = COLUMN_NAME 
            FROM #tempcolumns 
            WHERE num = @num
        SELECT * INTO #results FROM @tblname WHERE CAST(@colname AS VARCHAR) 
                    LIKE '%' + @term + '%' --this is where I'm having trouble
        UPDATE #tempcolumns
            SET printed = 1
            WHERE @num = num

    END

    SELECT * FROM #results
GO

This has two problems: first is that it gets stuck in an infinite loop somehow, and second I can't select anything from @tblname. I tried using dynamic sql as well, but I don't know how to get results from that or if that's even possible.

This is for an assignment I'm doing at college and I've gotten this far after hours of trying to figure it out. Is there any way to do what I want to do?

回答1:

1. You need to only search columns that actually contain strings, not all columns in a table (which may include integers, dates, GUIDs, etc).
2. You shouldn't need a #temp table (and certainly not a ##temp table) at all.
3. You need to use dynamic SQL (though I'm not sure if this has been part of your curriculum so far).
4. I find it beneficial to follow a few simple conventions, all of which you've violated:
   • use PROCEDURE not PROC - it's not a "prock," it's a "stored procedure."
   • use dbo. (or alternate schema) prefix when referencing any object.
   • wrap your procedure body in BEGIN/END.
   • use vowels liberally. Are you saving that many keystrokes, never mind time, saying @tblname instead of @tablename or @table_name? I'm not fighting for a specific convention but saving characters at the cost of readability lost its charm in the 70s.
   • don't use the sp_ prefix for stored procedures - this prefix has special meaning in SQL Server. Name the procedure for what it does. It doesn't need a prefix, just like we know they're tables even without a tbl prefix. If you really need a prefix there, use another one like usp_ or proc_ but I personally don't feel that prefix gives you any information you don't already have.
   • since tables are stored using Unicode (and some of your columns might be too), your parameters should be NVARCHAR, not VARCHAR. And identifiers are capped at 128 characters, so there is no reason to support > 257 characters for @tablename.
   • terminate statements with semi-colons.
   • use the catalog views instead of INFORMATION_SCHEMA - though the latter is what your professor may have taught and might expect.

CREATE PROCEDURE dbo.SearchTable
    @tablename NVARCHAR(257),
    @term      NVARCHAR(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'SELECT * FROM ' + @tablename + ' WHERE 1 = 0'; 

    SELECT @sql = @sql + ' 
      OR ' + c.name + ' LIKE ''%' + REPLACE(@term, '''', '''''') + '%'''
    FROM 
      sys.all_columns AS c
    INNER JOIN 
      sys.types AS t
      ON c.system_type_id = t.system_type_id
      AND c.user_type_id = t.user_type_id
    WHERE 
      c.[object_id] = OBJECT_ID(@tablename)
      AND t.name IN (N'sysname', N'char', N'nchar', 
        N'varchar', N'nvarchar', N'text', N'ntext');

    PRINT @sql;

    -- EXEC sp_executesql @sql;
END
GO

When you're happy that it's outputting the SELECT query you're after, comment out the PRINT and uncomment the EXEC.

回答2:

You get into an infinite loop because EXISTS(SELECT MIN(num) FROM #tempcolumns WHERE printed = 0) will always return a row even if there are no matches - you need to EXISTS (SELECT * .... instead

To use dynamic SQL, you need to build up a string (varchar) of the SQL statement you want to run, then you call it with EXEC

eg:

 declare @s varchar(max)
 select @s = 'SELECT * FROM mytable '
 Exec (@s)