SQL Server Performance Forum – Threads Archive

executing Dynamic SQL

Ok here is the code I run: declare @tName as varchar(100)
declare @cName as varchar(100)
declare @strSql as varchar(1000)
select @tName = ‘sites’
select @tName = ‘bitFlags’
select top 1 @tName = tname, @cName = cols from #finalCols select @strSql = ‘select * from ‘ + @tName + ‘ where ‘ + @cName + ‘ like ”%monsanto%” ‘
print @strSql exec @strSql
Here is the output: select * from Sites where BitFlags like ‘%monsanto%’
Server: Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure ‘select * from Sites where BitFlags like ‘%monsanto%’ ‘.
So it seems that the exec command cannot execute my stmt.
I looked into preparing the stmt but it didn’t workout very well. Any suggestion would be appreciated. Thx

---

I think you need to use exec (@strSql) Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

Yes, gaurav is right. And it’s even better to use sp_executsql instead of exec (since your string is far shorter than 4,000 characters, you’ll be ok)
In addition you might want to read two articles from a virtual friend of mine
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html HTH
Frank
http://www.insidesql.de
http://www.familienzirkus.de

---

]]>