Simple select using dynamic sql error

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

General SQL Server Forums

New to SQL Server Programming

Simple select using dynamic sql error

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2010-06-22 : 08:50:10

Good morning:

I have this query and want to translate to dynamic sql:

select * from sysobjects
where name like 'hechos%' and xtype = 'U'

using dynamic sql:

declare @name varchar(100)
declare @sql varchar(1000)
select @sql='select * from sysobjects'
select @sql=@sql + 'where' + @name + 'like ''hechos%'''
exec (@sql)

results commmand completely succeed, but it doesnt show me any result
it should display all the objects that start with the string "hechos"

if i used this query:
declare @sql varchar(1000)
select @sql='select * from sysobjects'
select @sql=@sql + 'where' + name + 'like ''hechos%'''
exec (@sql)

it yields an error mesage: name invalid column

what did i do wrong?

I'll appreciate your help

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-22 : 08:58:17

Statements must be..

declare @name varchar(100)
declare @sql varchar(1000)
Set @name='name'
select @sql='select * from sysobjects '
set @sql=@sql + 'where ' + @name + ' like ''hechos%'''
print @sql
exec (@sql)

declare @sql varchar(1000)
select @sql='select * from sysobjects '
select @sql=@sql + 'where name ' + 'like ''hechos%'''
print @sql
exec (@sql)

maintain necessary space and also make use of print command!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/