SQL - Searching across multiple columns instead of giving where columns separately

-- search using nvarchar column type 
DECLARE  
@tableName varchar(250) = 'tbl_test , 
@searchValue nvarchar(50) = 'prov' 
  
DECLARE 
@sql varchar(max) , 
@searchColumns varchar(max) =''  
  
   
SELECT @searchColumns = ISNULL(@searchColumns, '') + ' OR ' + QUOTENAME(COLUMN_NAME) +  
' Like ''%' + CAST(@searchValue AS varchar(10))+'%''' FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME=@tableName AND DATA_TYPE IN ('varchar', 'nvarchar') --you can modify this to look at other columns besides INT 
  

SELECT @searchColumns = STUFF(@searchColumns, 1, 4, '') 
  
  
DECLARE @resultTable AS TABLE (UnitDate DATETIME, PlantID NVARCHAR(50), CostCenter NVARCHAR(200)) 

SELECT @sql = 'SELECT * FROM ' + QUOTENAME(@tableName) + ' WHERE ' + @numericColumns 

INSERT INTO @resultTable 
EXEC(@sql) 

SELECT *  from @resultTable 

  
SELECT @sql = 'SELECT * FROM ' + QUOTENAME(@tableName) + ' WHERE ' + @searchColumns 

EXEC(@sql) 

Comments

Popular posts from this blog

Using External Content Types with Stored Procedures with Input Parameters

NAV 2009 Issues in Role Tailored Client