using variable in update... - SQL Server Forums

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

Transact-SQL (2005)

using variable in update...

Author

Topic

danielc
Starting Member

49 Posts

Posted - 2009-10-29 : 01:42:21

Hello all,

The following script is a stored proc that looks for a value occurrence in all tables. It returns the tables and field where the value is found into a temp table. I then added a cursor that allows me to update the fields that have the value I searched for. What I am having trouble with is the using the value stored in my string @ColumnValue2. When I run the stored procedure I get the error message, Error converting data type varchar to numeric. Here is the code I am using:

CREATE PROC [dbo].[SearchAllTables2]

(
@SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue 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
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

DECLARE @ColumnName2 nvarchar(128), @ColumnName3 nvarchar(128), @ColumnValue2 nvarchar(3630)
DECLARE @SQL nvarchar(1000)

DECLARE NTI_CURSOR CURSOR FOR
SELECT LEFT(ColumnName, 11), RIGHT(ColumnName, 4), ColumnValue
FROM #Results

OPEN NTI_CURSOR
FETCH NEXT FROM NTI_CURSOR INTO @ColumnName2, @ColumnName3, @ColumnValue2

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'UPDATE ' + @ColumnName2 + ' SET ' + @ColumnName3 + ' = '''' WHERE ' + @ColumnName3 + ' = ' + @ColumnValue2
EXEC (@sql)

FETCH NEXT FROM NTI_CURSOR INTO @ColumnName2, @ColumnName3, @ColumnValue2
END

CLOSE NTI_CURSOR
DEALLOCATE NTI_CURSOR

END

Thank you in advance for your help,

danielc

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-29 : 05:37:48

First I gotta say that I'm quite strongly opposed to the method you're using of updating these data. You might update A LOT more rows than you intend to by simply typing in a wrong search criteria. I would seriously reconsider this strategy.

When it comes to your problem I think it might have something to do with the dynamic update in the end; if you're resetting a numeric column (int, decimal, etc) the SET Column = '' will not work...you need to set it to NULL instead.

- Lumbago
http://xkcd.com/327/

Eli Kohen
Starting Member

6 Posts

Posted - 2009-10-29 : 08:19:44

Hi. Lumbago, it seems that this script has to update character columns only. But I think that the script tries to update the wrong column because of some strange string transformations under @ColumnName variable. I performed a simple test and the procedure failed with an syntax error in a dynamic sql execution of update query. Why didn't you used the separate columns in the result table for a table and column names?.. I debugged the procedure, the script with my fix is below.

DROP PROC [dbo].[SearchAllTables2]
go
CREATE PROC [dbo].[SearchAllTables2]

(
@SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (TableName nvarchar(370), ColumnName nvarchar(370), ColumnValue 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
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + ''',''' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

DECLARE @SQL nvarchar(1000), @ColumnValue nvarchar(3630);

DECLARE NTI_CURSOR CURSOR FOR
SELECT TableName, ColumnName, ColumnValue
FROM #Results

OPEN NTI_CURSOR
FETCH NEXT FROM NTI_CURSOR INTO @TableName, @ColumnName, @ColumnValue

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = '''' WHERE ' + @ColumnName + ' = ''' + @ColumnValue + ''''
EXEC (@sql)

FETCH NEXT FROM NTI_CURSOR INTO @TableName, @ColumnName, @ColumnValue
END

CLOSE NTI_CURSOR
DEALLOCATE NTI_CURSOR

END

Devart team. Database managment and data access solutions.
www.devart.com

danielc
Starting Member

49 Posts

Posted - 2009-11-02 : 13:11:25

Eli, thank you for your assistance with this block of code. I ran it and works just fine.

thanks,

Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources