Dreamweaver - Slight OT - Stored Procedure modification.

Author

Slight OT - Stored Procedure modification.

BrendanC

2004-08-26, 12:18 pm

Hi all.

I am using the Stored Procedure below, which I found on the SQLServerCentral
website, and it does exactly what its supposed to - display a list of all
tables and columns containing the search criteria.

The problem is instead of listing the table name and column name, I am trying
to modify it to return the table name and the id of the record that the search
criteria was found. The record id column of each of my tables is the table name
preceeded by 'pu'.

Anyone know what I am getting at, and if so how to go about solving the
problem?

Thanks greatly in advance.

Brendan

CREATE PROCEDURE spFindTextInColumns (@TableName sysname = NULL,
@StringToLookFor varchar(500))
AS
-- Example Calls
-- EXECUTE spFindTextInColumns MyTable, 'tcart' --search a specific table
--
-- EXECUTE spFindTextInColumns default, 'tcart' --search all tables
-- GO

DECLARE @columnName nvarchar(128),
@dateType nvarchar(128),
@row smallint,
@rowCount int,
@sql nvarchar(1000),
@puTable char(50)

DECLARE string_find_cursor CURSOR FAST_FORWARD FOR
SELECT [name]
FROM SYSOBJECTS
WHERE (
OBJECTPROPERTY(id, N'IsMsShipped') = 0
AND OBJECTPROPERTY(id, N'IsUserTable') = 1) AND (@TableName IS NULL OR
[name] = @TableName)
ORDER BY [name]

OPEN string_find_cursor

FETCH NEXT FROM string_find_cursor
INTO @tableName

SET @StringToLookFor = '%' + @StringToLookFor + '%'

WHILE @@FETCH_STATUS = 0
BEGIN
SET @row = 1

SELECT @rowCount = MAX([ORDINAL_POSITION])
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
GROUP BY [ORDINAL_POSITION]

WHILE @row <= @rowCount
BEGIN
SELECT @columnName = '[' + [COLUMN_NAME] + ']',
@dateType = [DATA_TYPE]
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
AND [ORDINAL_POSITION] = @row
ORDER BY [ORDINAL_POSITION]

SET @row = @row + 1

SET @sql = NULL

SET @puTable = 'pu' + @tableName

IF @dateType IN ( N'char', N'varchar', N'text')
SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' +
@StringToLookFor + ''', ' + @columnName + ') > 0'
ELSE IF @dateType IN (N'nchar', N'nvarchar', N'ntext')
SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' +
@StringToLookFor + ''', CAST(' + @columnName + ' As TEXT)) > 0'

IF @sql IS NOT NULL
BEGIN
SET @sql = 'IF EXISTS(' + @sql + ') PRINT ''[' + @tableName + '].' +
@columnName + ''''
--PRINT (@sql)
EXEC (@sql)
END
END

FETCH NEXT FROM string_find_cursor
INTO @tableName
END

CLOSE string_find_cursor
DEALLOCATE string_find_cursor
GO

Julian Roberts

2004-08-26, 12:18 pm

Try

SET @sql = 'SELECT *,' + @tableName + ' as TName FROM ' + @tableName + '
WHERE PATINDEX(''' +
@StringToLookFor + ''', ' + @columnName + ') > 0'

Sidenote: I guess you're using this as an Administrators tool rather a
search in front end of a website. The SP isn't very efficient. Cursors are
generally a no-no. Also, the use of the EXEC function means that the SP
isn't compiled.

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

BrendanC

2004-08-26, 12:18 pm

Jules

I'm afraid I am using it as a search from my web site. I have 4/5 different
tables where I have to search for the search term entered by the user.

I have tried searching through the web for help, but have only really come
accross the stored procedure detailed in my first message.

Do you have any links to tutorials which may be of use to me?

Thanks

Brendan

Julian Roberts

2004-08-26, 12:18 pm

You could use a UNION query or temp tables, not sure which is the most
efficient. For a UNION query, you'd have an SP like

create procedure mysearch
(@keyword nvarchar(100))
as
select field1 from table1
where field1 like '%' + @keyword + '%'
UNION

select field1 from table2
where field1 like '%' + @keyword + '%'

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

cmbergin

2004-08-26, 12:18 pm

Union is generally preferred, especially when using UNION ALL rather than
UNION. In this case, I think UNION ALL is the desired behavior anyway. (UNION
will check for and remove duplicate records from the result set; UNION ALL does
not perform this check, saving quite a bit of processing time. Since you're
searching 5 different tables, UNION ALL is likely correct.)

Singularity.co.uk

2004-08-26, 12:18 pm

Jules

The problem is that the results may link to different pages, in that the
result from one table is displayed on one page and the result from a second
table is displayed on a completely different page, due to the differing
number of columns in the table and the layout required.

So what I need returned ideally is the table name and record id of the
record containing the search text. Then I can link to the required page.

Hope I'm explaining myself ok, am still trying to get it all working clear
in my head.

Thanks for the help so far!

Brendan

--
___________________________________
Brendan.Collins@NOSPAMSingularity.co.uk
Remove the NOSPAM
http://www.singularity.co.uk
___________________________________

Julian Roberts

2004-08-26, 12:18 pm

That's right. But thinking about it further, it may be better to have

create procedure mysearch
(@keyword nvarchar(100))
as
set nocount on
select field1 from table1
where field1 like '%' + @keyword + '%'

select field1 from table2
where field1 like '%' + @keyword + '%'

and then use the NextRecordset() method in ASP. Particularly as each 'set'
would link to a different page. Debateable.

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

Singularity.co.uk

2004-08-26, 12:18 pm

Did you ever feel that the more you learn and ask, the less you feel you
actually know?

Will do a search for the NextRecordset() method as well as UNION ALL joins.

Thanks!

--
___________________________________
Brendan.Collins@NOSPAMSingularity.co.uk
Remove the NOSPAM
http://www.singularity.co.uk
___________________________________