Help in using Variable in CURSOR

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 2008 Forums

Transact-SQL (2008)

Help in using Variable in CURSOR

Author

Topic

shujaatali101
Starting Member

2 Posts

Posted - 2011-04-23 : 04:57:49

Hi
i have a code in which i search in all TABLES for a selected value name FERRAI the code is below

SET NOCOUNT ON

DECLARE @TABNAME VARCHAR(300)
DECLARE @COLNAME VARCHAR(300)
DECLARE @QUERY NVARCHAR(500)
DECLARE @COLCOUNT INT
DECLARE @TABCOUNT INT
SET @TABCOUNT =0

DECLARE _CURSOR CURSOR FOR
SELECT NAME FROM SYS.TABLES
--WHERE NAME LIKE 'TAB%'

OPEN _CURSOR

FETCH NEXT FROM _CURSOR INTO @TABNAME
SET @COLCOUNT = NULL
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE _COLCUR CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABNAME
AND DATA_TYPE IN('VARCHAR','CHAR','NVARCHAR') AND CHARACTER_MAXIMUM_LENGTH > 4

OPEN _COLCUR
FETCH NEXT FROM _COLCUR INTO @COLNAME

WHILE @@FETCH_STATUS = 0
BEGIN

SET @QUERY=N'SELECT @CNT=COUNT(*) FROM '+@TABNAME+' WHERE '+@COLNAME+' = ''FERRAI'''
EXEC SP_EXECUTEsql @QUERY,N'@CNT INT OUTPUT',@CNT = @COLCOUNT OUTPUT

PRINT @COLCOUNT
IF @COLCOUNT > 0
BEGIN
SELECT @TABNAME AS TABLENAME , @COLNAME AS COLUMNNAME
PRINT 'TABLE NAME : '+@TABNAME +' AND COLUMN NAME : '+@COLNAME + ' FOUND'
END
FETCH NEXT FROM _COLCUR INTO @COLNAME
END
CLOSE _COLCUR
DEALLOCATE _COLCUR

FETCH NEXT FROM _CURSOR INTO @TABNAME
SET @TABCOUNT = @TABCOUNT + 1

END

PRINT 'TOTAL TABLE : '+CAST(@TABCOUNT AS VARCHAR)

CLOSE _CURSOR
DEALLOCATE _CURSOR

i want to replace FERARRI with vairable @FIND i so replace code with

SET NOCOUNT ON

DECLARE @TABNAME VARCHAR(300)
DECLARE @COLNAME VARCHAR(300)
DECLARE @FIND VARCHAR(300)
DECLARE @QUERY NVARCHAR(500)
DECLARE @COLCOUNT INT
DECLARE @TABCOUNT INT
SET @TABCOUNT =0
SET @FIND = 'FERRARI'

DECLARE _CURSOR CURSOR FOR
SELECT NAME FROM SYS.TABLES
--WHERE NAME LIKE 'TAB%'

OPEN _CURSOR

FETCH NEXT FROM _CURSOR INTO @TABNAME
SET @COLCOUNT = NULL
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE _COLCUR CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABNAME
AND DATA_TYPE IN('VARCHAR','CHAR','NVARCHAR') AND CHARACTER_MAXIMUM_LENGTH > 4

OPEN _COLCUR
FETCH NEXT FROM _COLCUR INTO @COLNAME

WHILE @@FETCH_STATUS = 0
BEGIN

SET @QUERY=N'SELECT @CNT=COUNT(*) FROM '+@TABNAME+' WHERE '+@COLNAME+' = '+@FIND
EXEC SP_EXECUTEsql @QUERY,N'@CNT INT OUTPUT',@CNT = @COLCOUNT OUTPUT

PRINT @COLCOUNT
IF @COLCOUNT > 0
BEGIN
SELECT @TABNAME AS TABLENAME , @COLNAME AS COLUMNNAME
PRINT 'TABLE NAME : '+@TABNAME +' AND COLUMN NAME : '+@COLNAME + ' FOUND'
END
FETCH NEXT FROM _COLCUR INTO @COLNAME
END
CLOSE _COLCUR
DEALLOCATE _COLCUR

FETCH NEXT FROM _CURSOR INTO @TABNAME
SET @TABCOUNT = @TABCOUNT + 1

END

PRINT 'TOTAL TABLE : '+CAST(@TABCOUNT AS VARCHAR)

CLOSE _CURSOR
DEALLOCATE _CURSOR

it gives ERROR
Msg 207, Level 16, State 1, Line 1
Invalid column name 'FERRARI'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-23 : 06:53:42

You need to add single quotes on either side of the @FIND parameter as in:

WHILE @@FETCH_STATUS = 0
BEGIN

SET @QUERY=N'SELECT @CNT=COUNT(*) FROM '+@TABNAME+' WHERE '+@COLNAME+' = '+'''' + @FIND + ''''
EXEC SP_EXECUTEsql @QUERY,N'@CNT INT OUTPUT',@CNT = @COLCOUNT OUTPUT

When you want to include a single quote within a string, you have to escape it by inserting two single quotes, which is what I did above.

BUT, you may also want to check out this blog: http://beyondrelational.com/blogs/naomi/archive/2010/10/29/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx

Also, this free tool pack has something which will let you accomplish this. http://www.ssmstoolspack.com/

shujaatali101
Starting Member

2 Posts

Posted - 2011-04-24 : 02:35:08

Thank you it really Help me...........

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-25 : 04:55:05

If you want to search only string, also try
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources