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 5, 2013 at 7:30 am
Duplicate post. direct replies here. http://www.sqlservercentral.com/Forums/Topic1480760-3077-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply