April 23, 2013 at 5:12 am
I need to search a table and count instances of a particular string across multiple fields by date, I've used the following Dynamic SP in the past to look through fields and was hoping I could adapt it. I've tried different ways to change it to Count with no success, or am I barking up the wrong tree and this isn't the way to do it? I would like to keep it Dynamic so I don't have to specify field names as I may use this on other tables.
declare @TmpName as varchar (2000)
declare @TmpName1 as varchar (2000)
declare @sdate as varchar (20)
declare @svar as varchar (2000)
declare @CMD Nvarchar(100)
declare @Status1 as varchar(2000)
declare @sSearchterm as varchar(50)
set @sSearchterm = '%something%'
set @sDate = '29/Apr/2013'
DECLARE CUR1 CURSOR FAST_FORWARD FOR
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'resource'
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @TmpName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @CMD = 'SELECT @Rtn = [' + @TmpName + '] from [resource]' + ' where [date] = ''' + @sDate + ''''
exec sp_executesql @CMD,N'@Rtn varchar (200) out',@Status1 out
print @Status1
FETCH NEXT FROM CUR1 INTO @TmpName
END
CLOSE CUR1
DEALLOCATE CUR1
April 23, 2013 at 5:37 am
I've sort of cobbled something that works, my next question is how do I return the value @alcount rather than print it?
declare @TmpName as varchar (2000)
declare @TmpName1 as varchar (2000)
declare @sdate as varchar (20)
declare @svar as varchar (2000)
declare @CMD Nvarchar(100)
declare @Status1 as varchar(2000)
declare @alcount as int
set @alcount = 0
set @sDate = '29/Apr/2013'
DECLARE CUR1 CURSOR FAST_FORWARD FOR
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'resource'
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @TmpName
WHILE (@@FETCH_STATUS = 0)
BEGIN
if @tmpname <> 'Date' and @tmpname <> 'temp'
begin
SET @CMD = 'SELECT @Rtn = [' + @TmpName + '] from [resource]' + ' where [date] = ''' + @sDate + ''''
exec sp_executesql @CMD,N'@Rtn varchar (200) out',@Status1 out
if @Status1 like 'A/L%'
begin
set @alcount = @alcount + 1
end
end
FETCH NEXT FROM CUR1 INTO @TmpName
END
CLOSE CUR1
DEALLOCATE CUR1
print @alcount
April 25, 2013 at 6:46 pm
Mick,
Can you give us some kind of idea what your data looks like within the table? This will help us with a query. I've used CHARINDEX on a few instances to find certain text within fields.
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
April 25, 2013 at 9:55 pm
Here's what I came up with using dynamic SQL. To retrieve the column names dynamically it uses a function which I've included below.
DECLARE
@strSearch NVARCHAR(4000)
,@strSQL NVARCHAR(MAX)
,@strPath NVARCHAR(250)
,@pDate DATETIME
,@pDateSearchCol NVARCHAR(20)
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[ColVal] NVARCHAR(50) NULL,
PRIMARY KEY (ID))
SET @strSearch = N'%contentpane%' --ex 1
SET @strPath = N'LocalTestDB.dbo.Tabs' --ex1
--SET @strSearch = N'22%' --ex 2
--SET @strPath = N'LocalTestDB.dbo.TabModules' --ex2
SET @pDate = '2013-01-01'
SET @pDateSearchCol = 'LastModifiedOnDate'
SET @strSQL = N''
;WITH cteCols
AS
(
SELECT ORDINAL_POSITION, COLUMN_NAME
FROM dbo.itvfGetColumnNames(''+@strPath+'')
WHERE ORDINAL_POSITION > 0
)
SELECT
@strSQL = @strSQL +
N'SELECT '+ c.COLUMN_NAME + ' ' +
N'FROM '+@strPath+'' + ' ' +
N'WHERE DATEDIFF(day,'+@strPath+'.'+@pDateSearchCol+','''+CONVERT(VARCHAR(30),@pDate,121)+''') > 0 '+
N'AND ' + c.COLUMN_NAME + ' LIKE '''+@strSearch+'''; '+CHAR(10)+CHAR(13)
FROM
cteCols c
INSERT INTO #TempTable
EXEC sp_executeSQL @strSQL
SELECT COUNT(*) AS NumRows FROM #TempTable
CREATE FUNCTION [dbo].[itvfGetColumnNames]
(
@SourceFullPath SYSNAME
)
RETURNS TABLE
AS
RETURN
(
WITH Keys
AS (
SELECT
iso.COLUMN_NAME
,iso.COLUMN_DEFAULT
,iso.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.COLUMNS AS iso
WHERE
iso.TABLE_CATALOG = PARSENAME(@SourceFullPath,3)
AND iso.TABLE_SCHEMA = PARSENAME(@SourceFullPath,2)
AND iso.TABLE_NAME = PARSENAME(@SourceFullPath,1)
)
SELECT TOP 100 PERCENT
ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS rownum
,COLUMN_NAME
,COLUMN_DEFAULT
,ORDINAL_POSITION
FROM
Keys
ORDER BY
ORDINAL_POSITION
/*
SELECT * FROM dbo.itvfGetColumnNames('[FullyQualifiedThreePartDBName]')
*/
)
April 30, 2013 at 7:16 am
Sorry everyone that I haven't got back to you, I've been on holiday. I will be trying your solutions when my work settles down and get back to you all. The code looks great.. many thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply