T-SQL Store Procedure Table Variable
-
Hi, Running SQL server 2000 and working on a store procedure. Basically, I need to get the tbPayRate id in order to insert into another table but having several payrate tables for different tasks and it means @table varies. But got an error that I must declare the variable '@table'. Does anyone know how to get round this issue? DECLARE @table varchar(30) SET @table='tbPayRate' SELECT MAX(id) AS id FROM @table WHERE employee=5 Thanks, Joe
-
Hi, Running SQL server 2000 and working on a store procedure. Basically, I need to get the tbPayRate id in order to insert into another table but having several payrate tables for different tasks and it means @table varies. But got an error that I must declare the variable '@table'. Does anyone know how to get round this issue? DECLARE @table varchar(30) SET @table='tbPayRate' SELECT MAX(id) AS id FROM @table WHERE employee=5 Thanks, Joe
unfortunately you can not use variables for table names in sql statement. The work around is as follows
declare @sql varchar(255)
decalre @table varchar(30)set @table = 'tbPayRate'
set @sql = 'SELECT MAX(id) AS id FROM ' + @table + 'WHERE employee=5'Now that you constructed your sql statement dynamically, you can execute it
Exec(@sql)
will do it
Yusuf May I help you?
-
Hi, Running SQL server 2000 and working on a store procedure. Basically, I need to get the tbPayRate id in order to insert into another table but having several payrate tables for different tasks and it means @table varies. But got an error that I must declare the variable '@table'. Does anyone know how to get round this issue? DECLARE @table varchar(30) SET @table='tbPayRate' SELECT MAX(id) AS id FROM @table WHERE employee=5 Thanks, Joe
You must use a schema or database name to differentiate the different table E.g schemaname.tbPayRate or Databasename.SchemaName.Tablename you First declare a variable and set the what u r select to the value of the variable as shown below Declare @tbPayRateID varchar(50) set @tbPayRateID = (Select MAX(id) AS ID FROM schemaname.tbPayRate Where Employee = 5 Try this out