TSQL - help with script (MSSQL 2K)

[ecka00]

declare @foo varchar(40)
set @foo = 'user_mstr'

select * from @foo



result: Must declare the table variable "@foo".


please help!!! whenever i use the variable in a where statement i receive no errors. when as the tablename i received the above error.

[rmiao]

Try dynamic sql like:

declare @foo varchar(40)
declare @cmd varchar(40)
set @foo = ''user_mstr'
select @cmd = 'select * from ' + @foo
exec(@cmd)

[jezemine]

don't use dynamic sql without reading this first:

http://www.sommarskog.se/dynamic_sql.html

[ecka00]

rmiao, first of all thank you for responding to my post yesterday with:

declare @foo varchar(40)
declare @cmd varchar(40)
set @foo = 'user_mstr'
select @cmd = 'select * from ' + @foo
exec(@cmd)

however, one more thing; what if i want to add a like statement? eg.,

select @cmd = 'select * from ' + @foo + 'where visit_no not like where visit_no not like '[0-9]%''

the single quotes are my problem in the like statement wrapped in single quotes for the @cmd

[ecka00]

nevermind, i figured it out---thanks again for pointing in the right direction.

[ecka00]

FYI---in case you're interested:

select @cmd = 'select * from ' + @tblname + ' where visit_no not like ''' + @zena + ''''

[rmiao]

Try this with double quotes:

select @cmd = 'select * from ' + @foo + 'where visit_no not like where visit_no not like "[0-9]%'''