August 4, 2013 at 12:16 am
Please see these t-sql code:
ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630),DocNo nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @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
AND @TableName IN ('[dbo].[Header]','[dbo].[Padid]','[dbo].[Publisher]','[dbo].[rade]',
'[dbo].[Subjects]','[dbo].[Title]','[dbo].[Description1]')
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
,'+@TableName + '.DocNo' +' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE CONTAINS( ' + @ColumnName + ' , ' + @SearchStr2+')'
)
END
END
END
SELECT document.DocNo FROM Document INNER JOIN #Results ON #Results.DocNo=document.DocNo COLLATE DATABASE_DEFAULT
END
this stored proc show docno .
and see this(I named it Code-One) :
DECLARE @DocNo nvarchar(10)
DECLARE @RadeType nvarchar(20)
SELECT @RadeType = DefultSetting.Def_Rade FROM DefultSetting;
SELECT document.DocNo ,document.DocType,Title.Title,Header.WriterName + ' '+
Header.WriterName AS 'Padid' ,Publisher.PublisherName,Publisher.PublishedDate
,Rade.MainRange + ',' + Rade.Num +','+Rade.KaterNO +','+Rade.Date1 AS 'Rade'
FROM Document LEFT OUTER JOIN Title ON document.DocNo = Title.DocNO
LEFT OUTER JOIN Header ON document.DocNo = Header.DocNo
LEFT OUTER JOIN rade ON document.DocNo = rade.DocNO
LEFT OUTER JOIN Publisher ON document.DocNo = Publisher.DocNo WHERE Rade.Type = @RadeType
AND document.DocNo=@DocNo
Where @DocNo be the output proc .namely I want to have some things like this at the end line of proc:
SELECT GetInfo(document.DocNo) FROM Document INNER JOIN #Results ON #Results.DocNo=document.DocNo COLLATE DATABASE_DEFAUL
such that getinfo is a function that works like Code-One.
How Can I do this?
August 4, 2013 at 4:01 pm
I find it very difficult to understand what you are doing and what you are trying to achieve. It seems that you are searching a lot of tables for some search string - which indicates a bad database design to start with.
Furthermore, this looks spooky to me:
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
...
' WHERE CONTAINS( ' + @ColumnName + ' , ' + @SearchStr2+')'
In full-text, % has no particular meaning as I recall. The wildcard for full-text is *. You can have trailing wild-card, but not leading if memory serves.
And the thing about code-one and @DocNo, I don't understand at all. Couldn't you just use a output parameter?
Hm, you may have multiple matches, and you cannot use INSERT-EXEC as you already use it inside the procedure. If that is the issue, see this article on my web site for tips: http://www.sommarskog.se/share_data.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 4, 2013 at 6:37 pm
Explains:
My Database is for book store.I have 7 tables that stored book info on theirs.
The Stored Procedure SearchAllTable search tables for any string .for example if we want to retrieve information about those book that have word "Network" in any fields,We execute searchalltable with @searchstr='Network';
Stored Proc return docno -- document number-- of books.
Now,I want have other info of books,for example Title,Publisher,etc.so if I Change
SELECT document.DocNo FROM Document INNER JOIN #Results ON #Results.DocNo=document.DocNo COLLATE DATABASE_DEFAULT
to
SELECT document.DocNo ,document.DocType,Title.Title,Header.WriterName + ' '+
Header.WriterName AS 'Padid' ,Publisher.PublisherName,Publisher.PublishedDate
,Rade.MainRange + ',' + Rade.Num +','+Rade.KaterNO +','+Rade.Date1 AS 'Rade'
FROM Document LEFT OUTER JOIN Title ON document.DocNo = Title.DocNO
LEFT OUTER JOIN Header ON document.DocNo = Header.DocNo
LEFT OUTER JOIN rade ON document.DocNo = rade.DocNO
LEFT OUTER JOIN Publisher ON document.DocNo = Publisher.DocNo WHERE Rade.Type = @RadeType
AND document.DocNo = SELECT document.DocNo FROM Document INNER JOIN #Results ON #Results.DocNo=document.DocNo COLLATE DATABASE_DEFAULT
The Store Proc Return All information of books.I like instead doing this change I have a function or Everything else that to have :
SELECT GetInfo(document.DocNo) FROM Document INNER JOIN #Results ON #Results.DocNo=document.DocNo COLLATE DATABASE_DEFAULT
August 5, 2013 at 1:03 am
Eskandari (4/8/2013)
My Database is for book store.I have 7 tables that stored book info on theirs.
Why seven tables instead of a single one?
I'm still at loss what you want to do. It almost seems that you want to replace
SELECT document.DocNo ,document.DocType,Title.Title,Header.WriterName + ' '+
Header.WriterName AS 'Padid' ,Publisher.PublisherName,Publisher.PublishedDate
,Rade.MainRange + ',' + Rade.Num +','+Rade.KaterNO +','+Rade.Date1 AS 'Rade'
FROM Document LEFT OUTER JOIN Title ON document.DocNo = Title.DocNO
LEFT OUTER JOIN Header ON document.DocNo = Header.DocNo
LEFT OUTER JOIN rade ON document.DocNo = rade.DocNO
LEFT OUTER JOIN Publisher ON document.DocNo = Publisher.DocNo WHERE Rade.Type = @RadeType
AND document.DocNo=@DocNo
SELECT GetInfo(document.DocNo)
FROM Document
INNER JOIN #Results ON #Results.DocNo=document.DocNo
COLLATE DATABASE_DEFAULT
But that makes little sense. Not the least because the first query returns many columns and the latter returns only one.
And what is this GetInfo?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 5, 2013 at 2:56 am
hi,
one specific writer may write some books or one publisher published many books and etc etc.so we have must have 7 tables.
August 5, 2013 at 3:05 am
Not sure that I get this. Yes, you need more than one table in the database. But why you would need to search seven tables is not clear to me.
Rather than posting code which is difficult to grasp, describe the actual problem. Like "I have the tables Apples (which holds information about ...), Bananas (....). I want to write a function that ...".
Maybe the actual solution to your problem is not in the direction you started going.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 5, 2013 at 3:33 am
The basic problem is this:
Consider the case that we are looking for a particular term, for example, the word network.
A word can be located anywhere, such as the subject and ...
I wrote a function that will search all fields in Table 7 and the books number in the returned.
Now I want to get other information such as publishers and etc from these returned number
August 5, 2013 at 1:52 pm
Scrap your function with dynamic SQL that loops tables. Just write a stored procedure that issues a query for each table. It's easier to understand and maintain.
Since I don't know how your tables are structured, I cannot really say how you should retrieve the information. It would help if you posted the CREATE TABLE statements for your tables.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 6, 2013 at 6:06 am
Eskandari (8/5/2013)
The basic problem is this:Consider the case that we are looking for a particular term, for example, the word network.
A word can be located anywhere, such as the subject and ...
I wrote a function that will search all fields in Table 7 and the books number in the returned.
Now I want to get other information such as publishers and etc from these returned number
Please read the link in my signature to help you post your question in a format which will allow others to generate working solutions for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply