June 2, 2009 at 5:57 am
See the script. When passing @tname (written to simply be a nvarchar that holds names of tables) sp_executesql thinks its a table variable. Is there special syntax to avoid this issue?
drop database test
go
create database test
go
use test
go
create table test1 (lkey int identity(1,1), [myData] varchar(50))
go
create table test2 (lkey int identity(1,1), [myData] varchar(50))
go
create table test3 (lkey int identity(1,1), [myData] varchar(50))
go
insert into test1
(myData) values ('test value 1')
insert into test2
(myData) values ('test value 2')
insert into test3
(myData) values ('test value 3')
set nocount on;
declare @myt table (lkey int identity (1,1), tablename nvarchar(200), columnname nvarchar(200))
declare @counter int,
@reccountout int,
@reccount int,
@searchInput nvarchar(100),
@tablename nvarchar(200),
@columnname nvarchar(200),
@match int,
@foundcounter int
DECLARE @SQLString nvarchar(4000);
DECLARE @ParmDefinition nvarchar(4000);
--change this to the needed string, keep % sign if not exact match wanted.
set @searchInput = '%test value 2%'
set @foundcounter = 0
insert into @myt
select t.name,c.name from sys.tables t
join sys.columns c on t.object_id = c.object_id
where c.system_type_id <> 34
order by t.name, c.name
select @reccountout = (select count(lkey) from @myt)
print 'searching for ' + @searchinput
print '=================================='
select @counter = 1
while @counter <= @reccountout
begin
set @tablename = (select tablename from @myt where lkey = @counter)
set @columnname = (select columnname from @myt where lkey = @counter)
set @sqlstring = 'select @matchout = count(*) from @tname where @cname like ''@search'''
set @ParmDefinition = '@tname nvarchar(200), @cname nvarchar(200), @search nvarchar(100), @matchout int output'
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@tname = @tablename,
@cname = @columnname,
@search = @searchinput,
@Matchout = @Match output
--print @match
if @match > 0
begin
print @tablename + '.' + @columnname + ' contains matching data'
set @foundcounter = @foundcounter + 1
end
set @counter = @counter + 1
end
if @foundcounter = 0
begin
print 'no match found'
end
use master
go
Jimmy
"I'm still learning the things i thought i knew!"June 2, 2009 at 6:05 am
Parameters in sp_executesql are only allowed in the places they're allowed in normal SQL. If you want the table name to be a variable, you'll have to concatenate that value into the string. You're going to have to do the same with the column name
Instead of
set @sqlstring = 'select @matchout = count(*) from @tname where @cname like ''@search'''
it'll have to be
set @sqlstring = 'select @matchout = count(*) from ' + @tablename + ' where ' + @cname + ' like @search'
Also, you don't need quotes around @Search. It's a parameter. With quotes, SQL's going to treat it as a string literal
Beware SQL injection
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 6:21 am
Thanks GilaMonster,
I already had it working in a similar way but was trying to eliminate any sql injection as you mentioned at the bottom of your reply post. I still used sp_executesql with the below string that was very similar to yours. I think that's where my mind was still stuck on the quotes. Based upon your post there is nothing more that can be done and I understand the reasoning behind it.
Thanks again for the awesomely quick reply post. Now i can work on something else.
set @sqlstring = 'select @matchout = count(*) from ' + @tablename + ' where ' + @columnname + ' like ''' + @searchinput + ''''
Jimmy
"I'm still learning the things i thought i knew!"Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply