CONVERT error - Money to Varchar
-
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
This stored procedure does not search numeric or money types.
By adding numeric, I was able to expand it to search numeric values.
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar','numeric')
Then I tried to expand it to do money type.
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar','numeric','money')
It seems to locate the money field just fine, but there is a money to varchar
error. It appears to be something in this section of the code, but I
can't figure it out.
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
 
END
-
The stored procedure is designed to search for columns and records where a supplied string is contained within the column.
Numeric datatypes won't work in this way. What exactly is it you want to do with the numeric columns?
-
You should contact Vyas directly.
He's a nice guy and should be more than willing to improve his code.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Actually it will work, if the following line is modified:
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar','numeric')
So by adding ,'numeric' I can search numeric field types also. I need to search money field types. When I add the money type, I get an error from the part of the stored procedure I listed in the first post.
Something in that part of the stored procedure needs to use CONVERT.
Ultimately what I'm trying to do is trace the data flow, in a large database, with multiple tables and joins that are designed to force custom programming back to the vendor. I need to understand the dataflow, because I'm mapping one system to talk to another system, in real time.
-
To claify, it will work for numeric type, not money.
I did send the developer of the stored procedure an email. If he sends me anything, I'll post it here.
-
The error that you get is on the Left function that can't perform well with a money type!
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
To double check just Try:
select Left (cast (3 as char) , 3630)
select Left (cast (3 as varchar) , 3630)
select Left (cast (3 as nchar) , 3630)
select Left (cast (3 as nvarchar) , 3630)
select Left (cast (3 as numeric) , 3630)
select Left (cast (3 as money) , 3630) -- error
* Noel -
Is there any other command that would work in place of CAST?
-
This does the trick with CAST
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + 'AS VARCHAR(3630)), 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE CAST(' + @ColumnName + ' AS VARCHAR(3630)) LIKE ' + @SearchStr2
)
END
and this is the same thing with CONVERT
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(VARCHAR(3630),' + @ColumnName + '), 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE CONVERT(VARCHAR(3630),' + @ColumnName + ' ) LIKE ' + @SearchStr2
)
END
-
Thanks, works well. This is a pretty neat utility to find items across all tables of a large database.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply