Link to home
Start Free TrialLog in
Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on

SQL Type Conversion

I have modified the code to find text in tables to what you see below.

SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'') PRINT ''select * from ' + @table_name + ' where ' + @column_name + ' like ''%' + @search_string  + '%''' + ''''

I am getting this error message on the second occurence of @search_string.

Conversion failed when converting the varchar value 'select * from IV30500 where DOCNUMBR like ' to data type int.

I believe what is happening is that often the value can be all numeric so SQL converts it to a numeric type field as opposed to just treating it as all text. How do I adjust this portion to simply treat whatever is read as text? Do I need to put a case statement that looks for all digits and then converts to string while leaving any values that do have characters alone? If so, what would the syntax look like?
Avatar of Om Prakash
Om Prakash
Flag of India image

SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'') PRINT ''select * from ' + @table_name + ' where ' + @column_name + ' like ''%' + @search_string  + '%''' + ''''

Most probably you should not get errors if all the variables used above are declared as varchar.  if there is a numeric field, you can convert as
CAST(@search_string AS VARCHAR(200))
Avatar of Pratima
I think error is not for the search string
it is for sql_string


Conversion failed when converting the varchar value 'select * from IV30500 where DOCNUMBR like ' to data type int.

as it is saying failed to convert whole sql that is created to int

can share complete code with us
Please post your complete code...
Avatar of rwheeler23

ASKER

It is the execute SQL that actuall fails. Here is the complete code. All I Am trying to do is to get thise code to produce the actual select statements I need to look for data in tables. So the end result may be lines that read

select * from MyTable where MyColumn='123456' or
select * from MyTable where MyColumn='abcdef'
FindTextInTables.txt
I guess you are looking for script to search particular value  in whole DB...
I have developed my own code....which will perform better then cursor.. have a look and let me know if you have nay concern...

declare @search_string nvarchar(200),@sql nvarchar(max) ,@object_id bigint

set nocount on 

set @search_string='Kentucky'

Create table #temp_t
( object_id bigint default 0,
	query nvarchar(max)
 )

insert into #temp_t (object_id,query)
select s.OBJECT_ID, 'if exists ( select 1 from ' +  object_name(s.object_id) + ' where '  + s.name + ' like  ''%' + @search_string +'%'' )  '+ Char(13) + 
        'Print  ''select * from ' +  object_name(s.object_id) + ' where ' + s.name + ' like  ''''%' + @search_string + '%'''''';' 
 from sys.columns s  join  sys.objects o on s.object_id=o.object_id where o.type='U'


While @@ROWCOUNT > 0
begin
set @sql=''
set @object_id = (select top(1)  t.object_id from #temp_t t )
if @@ROWCOUNT=0
break;

select @sql= @sql + Case when @sql > '' then CHAR(13) else '' end + query from #temp_t t where t.object_id=@object_id
--print @sql
exec (@sql)

Delete from #temp_t  where object_id=@object_id 
end 



drop table #temp_t

Open in new window


Thanks,
saurabh
In addition to looking for text in tables I then want to build the SQL select statements so I can actually seen the data records in each table that it is found. I have a database with 1,500 tables and those tables have relationships. I am just looking for something to build those select statements to aid in the data discovery process.

I will attempt to modify your code later today.
Sure ...let me know if you need any help
That code is hideously inefficient.

The code should be written such that:

1) it does not check impossible columns
2) it checks all columns for one table at the same time
Just for the record, when I ran the original script it took about 30 minutes hour on an 18GB database. I currently have the revised verison running and it has been over 1 1/4 hours. I appreciate the help. I keep getting asked to do this so I am trying to save myself time by having the Select statement created by the search. Can my original script be corrected so the Execute statement will execute correclty?
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you. I will give these a try later today and let you know.
Thank you. I forgot this was not awarded