searching multiple tables with multiple strings-

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

searching multiple tables with multiple strings-

Author

Topic

blessy
Starting Member

6 Posts

Posted - 2011-09-14 : 01:21:12

Dear All,
Below is the keyword search that i did perform. unfortunately it doesn't produce result. Can any one please help me to complete it.
SplitString is a function wwhich separates search keywords.

create PROC [dbo].[KeywordSearchsample]
(
@SearchStr nvarchar(100)

)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
create table #temptable (id int identity(1,1),col1 varchar(255))

insert into #temptable
select * from SplitString(@SearchStr,',')

SET NOCOUNT ON
DECLARE @count int
SET @count =(select count(*) from #temptable)
print @count
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

WHILE(@COUNT >0)
BEGIN

SET @TableName = ''
SET @SearchStr2 = QUOTENAME('IN %' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + 'in( ''+select col1 from #temptable + '' ) '
)
END
end

SET @COUNT=@COUNT-1
END

END

SELECT ColumnName, ColumnValue FROM #Results
END

Blessy K John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 03:42:39

replace the EXEC with PRINT and post the resulting query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blessy
Starting Member

6 Posts

Posted - 2011-09-14 : 05:03:58

hi is there any way to combine in and like operator?
I would like to do the following query

SELECT * FROM tbl1 WHERE name in(select col1 from #temptable )

#temptable contains 5 or 6 values. I want to retrieve all such fields in tbl1

Blessy K John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:22:45

you can do as

SELECT * FROM tbl1 t1
inner join #temptable t
on t1.name like '%' + t.col1 + '%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources