selec * from @variable_table - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

selec * from @variable_table

Author

Topic

vreezman
Starting Member

2 Posts

Posted - 2004-12-15 : 21:38:14

i have a tables with sequence name
like journal_01_04
journal_02_04
.....
journal_12_04
is that possible for me to make stored procedure that access certain table
just by passing table name as parameter, ex : if i pass parameter "journal_01_04"
then it process table journal_01_04. i know i can solve this problem with "select case"
but the code is too looong.....

Hippi
Yak Posting Veteran

63 Posts

Posted - 2004-12-15 : 21:54:11

Yeap, u can use dynamic sql. Try to search for dynamic sql in this forum

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-15 : 22:25:56

see
http://www.nigelrivett.net/SQLTsql/TableNameAsVariable.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-15 : 22:37:47

Declare @sql as NVarchar(500), @TableName as Varchar(100)
Set NoCount On
Select [Name] as TableName into #MyTables From sysobjects where xtype ='U' and Name like 'journal%'

While Exists(Select * from #MyTables)
BEGIN
Select Top 1 @TableName = TableName From #MyTables
Delete From #MyTables Where TableName = @TableName
Set @sql = 'Select * from ' + @TableName
Print @sql
exec sp_executesql @sql
END --WHILE TABLE
Drop Table #MyTables

Hope that helps and you don't even have to use cursors.

vreezman
Starting Member

2 Posts

Posted - 2004-12-16 : 21:44:16

clarkbaker1964 :

sorry i dunno understand u code :(, i'm a newbee in t sql, could u write a code to
loop the recordset of dynamic sql :
like i want to loop an recordset of "select * from @tablename"

thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources