Multiple columns search with some blank/null values

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Multiple columns search with some blank/null...

Multiple columns search with some blank/null values

Rate Topic

Display Mode

Topic Options

Author

Message

Hem.Singh

Posted Sunday, September 09, 2012 5:05 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, November 03, 2012 5:43 AM
Points: 21, Visits: 67

Hi,

I have a Form of multiple fields and there are some fields that are optional. How I can perform that search with Sql stored procedure. Because user can fill one column or all columns. So how I will handle this on Sql Server.

Thanks & Regards.
Hem Singh

Thanks & Regards,
Hem Singh

Post #1356459

J Livingston SQL

Posted Sunday, September 09, 2012 5:23 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:10 PM
Points: 1,472, Visits: 14,602

here is an excellent article on "catch all queries".
well worth a read for you I think

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

kind regards

__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________

Post #1356461

Hem.Singh

Posted Tuesday, October 16, 2012 11:46 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, November 03, 2012 5:43 AM
Points: 21, Visits: 67

@J Livingston thanks for the reply.

there are multiple solutions in the link you provided. still thinking which one is best.

Regards,
Hem Singh

Thanks & Regards,
Hem Singh

Post #1373633

Sean Pearce

Posted Wednesday, October 17, 2012 5:05 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 386, Visits: 1,425

Hem.Singh (10/16/2012)

there are multiple solutions in the link you provided. still thinking which one is best.

Test, test, test

The SQL Guy @ blogspot

About Me

Post #1373730

Bhuvnesh

Posted Wednesday, October 17, 2012 5:59 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

Hem.Singh (10/16/2012)

there are multiple solutions in the link you provided. still thinking which one is best.

YOu have to tuse something like this

Where Col1 = @v1 OR Col2 = @v2 OR Col3 = @v3 Etc

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1373745

Sean Pearce

Posted Wednesday, October 17, 2012 6:07 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 386, Visits: 1,425

Bhuvnesh (10/17/2012)

Hem.Singh (10/16/2012)

there are multiple solutions in the link you provided. still thinking which one is best.

YOu have to tuse something like this

Where Col1 = @v1 OR Col2 = @v2 OR Col3 = @v3 Etc

Some parameters are optional so that won't work.

The SQL Guy @ blogspot

About Me

Post #1373749

Hem.Singh

Posted Wednesday, October 17, 2012 11:32 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, November 03, 2012 5:43 AM
Points: 21, Visits: 67

I have used dynamic SQL query.... because there is written that there is no fear of Sql injection because Query is still using parameters. Fot this time i have implemented it for Update query... will use for search later... but concept is clear...

does anyone want to say more about dynamic sql?

Thanks & Regards,
Hem Singh

Post #1374143

vikingDBA

Posted Friday, October 19, 2012 2:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:34 AM
Points: 189, Visits: 864

Don't know if it will help, but here is a script I wrote that loops through all tables, and creates the script to look for a particular string in any column of the listed data types. Just run this, after setting the database to use, and the string to search for, and it will create the SELECT scripts for you. Big time saver. And, for a bonus, it prints messages if anything is found in any of the tables (IF EXISTS). That way you know which ones to concentrate on.

/* ============================================================================================================== */
/* Create scripts to search each table's string columns for specified string value */
/* ============================================================================================================== */
/* Created Date: 08/29/2012
By: VikingDBA
Modifications:

Dependencies:

Summary:
This script automates the creation of scripts to search each table, and its string columns, for a specific string value. Just
set the variable @sfi to be the string to search for. If other data types need to be searched, just add them to the list
of data types that is in the code.

This creates scripts for all tables.

*/

USE AdventureWorks -- Set the database context
GO

SET NOCOUNT ON

DECLARE @sn varchar(128)
DECLARE @tn varchar(128)
DECLARE @de varchar(128)
DECLARE @cmd varchar(4000)
DECLARE @dt varchar(128)
DECLARE @sfi varchar(4000)
DECLARE @tt varchar(128)

/* ================================================================================================= */
--User Settable Variables
SET @sfi = 'something weird to search for' -- What string to search for
/* ================================================================================================= */

SELECT CONVERT(varchar(128),TABLE_SCHEMA) AS 'SchemaName',
CONVERT(varchar(128),TABLE_NAME) AS 'TableName',
CONVERT(varchar(128),COLUMN_NAME) AS 'DataElement',
CONVERT(int,ORDINAL_POSITION) AS 'OrdinalPosition',
CONVERT(varchar(128),DATA_TYPE) AS 'DataType',
CONVERT(varchar(128),'') AS TableType
INTO #dummycol
FROM information_schema.columns gg
ORDER BY SchemaName, TableName, OrdinalPosition

UPDATE #dummycol SET TableType = 'VIEW' WHERE EXISTS (SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = #dummycol.SchemaName AND TABLE_NAME = #dummycol.TableName AND TABLE_TYPE = 'VIEW')

SELECT * FROM #dummycol

PRINT '-- If values exist in particular table or view, just highlight the select statement and run for desired table to get exact rows'

DECLARE myCursorVariable CURSOR FOR
SELECT DISTINCT SchemaName, TableName, TableType FROM #dummycol ORDER BY SchemaName, TableName

OPEN myCursorVariable

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @sn, @tn, @tt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ''

DECLARE myCursorVariable2 CURSOR FOR
SELECT DataElement, DataType FROM #dummycol WHERE SchemaName = @sn AND TableName = @tn ORDER BY OrdinalPosition

OPEN myCursorVariable2

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable2 INTO @de, @dt

WHILE @@FETCH_STATUS = 0
BEGIN

if @dt IN ('char','varchar','text','ntext','nchar','nvarchar')
BEGIN
if @cmd <> '' SET @cmd = @cmd + CHAR(13)
SET @cmd = @cmd + 'OR [' + @de + '] LIKE ' + '''' + '%' + @sfi + '%' + ''''
END

FETCH NEXT FROM myCursorVariable2 INTO @de, @dt
END

CLOSE myCursorVariable2
DEALLOCATE myCursorVariable2

if @cmd <> ''
BEGIN
if @tt = 'VIEW'
PRINT CHAR(13) + CHAR(13) + '--View'
else
PRINT CHAR(13) + CHAR(13)
PRINT 'if EXISTS ('
PRINT 'SELECT * FROM ' + @sn + '.' + @tn + CHAR(13) + 'WHERE ' + SUBSTRING(@cmd,4,LEN(@cmd) -3)
PRINT ')'
PRINT CHAR(9) + 'PRINT ''Records found in ' + @sn + '.' + @tn + ''''
END

FETCH NEXT FROM myCursorVariable INTO @sn, @tn, @tt
END

CLOSE myCursorVariable
DEALLOCATE myCursorVariable

DROP TABLE #dummycol
SET NOCOUNT OFF

Post #1375037

« Prev Topic | Next Topic »

Permissions