Multiple columns search with some blank/null values
-
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 -
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
and remember....every day is a school day -
@j-2 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 -
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
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 EtcSome parameters are optional so that won't work.
-
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 -
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply