sql: how to select a row, if the input can be in any field/column of a table?

Question

If I have this table TableABC and it has some fields/columns, rather say it has many fields/columns col1, col2, col3..... say upto 10 or 20, the count doesn't matter right now. The point is, I wanna know, if we ask a user for input, say @input varchar(max) or something like this, what I wanna know is that..

How can I select a row, if this input occurs in any column in that row, I mean the row would be selected if col1=@input also if col2=@input and so on..

Now as I said the count of column doesn't matter, what matters is we don't know ahead of time the columns so that we may do something like

where
col1 = @input
or
col2 = @input

we can't do that. I got as far as getting the names of column from table by say

SELECT [name] FROM sys.Columns WHERE [Object_id]=Object_id(N'TableABC')

This would give us names of col in the required table, now I don't know what can we do with them, or is this even useful in the scenario I am describing? So, what's the solution?

Lastly, in case someone might ask, where am I gonna use it, or what's my requirement or something, let me clear its not for (at least as of now!) practical implementation, I just wanna know if its possible, and if so, how? I hope I made myself clear.

You can have a look here: http://stackoverflow.com/questions/709120/how-to-search-for-one-value-in-any-column-of-any-table-inside-one-ms-sql-databas — Adrian Fâciu, Aug 28 '12 at 06:55
Is the goal to find the value in a mess of columns or to build a search against all the columns. If the former rather than mess around with a complex query; I would add a computed column and put the values of all rows in it; or use full text search which is great at searching masses of columns in a single * bound. — u07ch, Aug 28 '12 at 07:06

score 1 · Accepted Answer · answered Aug 28 '12 at 06:59

You can use cursor to iterate through column names as you suggested and then build dynamic query, later exec it, something like this:

DECLARE @Expression varchar(max)
DECLARE @ColumnName varchar(max)
SELECT @Expression = 'WHERE '

DECLARE db_cursor CURSOR FOR SELECT [name] FROM sys.Columns WHERE [Object_id]=Object_id(N'TableABC')
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @ColumnName   

WHILE @@FETCH_STATUS = 0   
BEGIN
    SELECT @Expression = @Expression + @ColumnName + ' = ' + @input + ' AND '

    FETCH NEXT FROM db_cursor INTO @ColumnName   
END 

CLOSE db_cursor   
DEALLOCATE db_cursor

SELECT @Expression
-- EXEC(@Expression) etc...

score 0 · Answer 2 · answered Aug 28 '12 at 07:01

You can check for each column in the stored procedure and generate Where condition using IF..ELSE conditions:

if @Col1 is not null  
begin   
print 'type'
if @Cond is not null
    Set @Cond = @Cond + ' and '

    SET @Cond =@Cond + ' col1 =''' +@Col1 +''''  
end

score 0 · Answer 3 · answered Aug 28 '12 at 13:00

If you are looking for the customers that modified something in any of the fields, you should be looking into selecting from the table into an XML doc, and then do some manipulation on the document. I've attached an example, you can modify it to serve your purpose.

I am sure there are many XML gurus here that can write this better, but this would be the general idea. No loops, no cursors.


/*

    CREATE SOME DUMMY DATA
*/  

CREATE TABLE _TEST (CustomerID int, COL1 varchar(255), COL2 varchar(255),COL3 varchar(255),COL4 varchar(255))

INSERT INTO _TEST SELECT 101, NULL, NULL, NULL, NULL
INSERT INTO _TEST SELECT 102, NULL, NULL, NULL, NULL
INSERT INTO _TEST SELECT 103, NULL, 'input test', NULL, NULL
INSERT INTO _TEST SELECT 104, NULL, NULL, NULL, 'input test'
INSERT INTO _TEST SELECT 105, NULL, NULL, NULL, NULL
INSERT INTO _TEST SELECT 106, 'input test', NULL, NULL, NULL

/*
    FORM THE XML
*/

DECLARE @xmldoc XML

SET @xmldoc = (select * from _TEST FOR xml raw,root('Sample')) 

/*
    GET THE CUSTOMERS THAT CHANGED SOMETHING IN ANY OF THE FIELDS
*/

SELECT CUSTOMERID FROM 
    (
        select
           X.value('@CustomerID[1]','int') as CUSTOMERID,
           X.query('count(@*[2])').value('.','int') as MODIFIED
        from
           @xmldoc.nodes('/Sample/row') as A(X) 
    ) RES
WHERE 
    MODIFIED = 1

I hope this helps, Dan

sql: how to select a row, if the input can be in any field/column of a table?

3 Answers3