I am just trying to run a stored procedure in SQL Server 2000. Its a vary basic SP except that I need the table name to be a variable. I tried this;
-- ============================================= -- Create procedure basic template -- ============================================= -- creating the store procedure IF EXISTS (SELECT name FROM sysobjects WHERE name = N'testing' AND type = 'P') DROP PROCEDURE testing GO
CREATE PROCEDURE testing AS begin declare @tablename varchar(50) set @tablename = 'IT2NETSQL01CHA.PSQL_GCSS0001_DEV.dbo.SEC_LOG' SELECT * FROM [@tablename] end GO
I get the following error:
Server: Msg 208, Level 16, State 1, Procedure testing, Line 7 Invalid object name '@tablename'.
If I try the variable without , I get a syntax error. Could someone tell me what is the correct way to do this ?
Not a surprising problem, most stored procedure languages rigourously seperate data (variables and values) from code (SQL). However, most do have some way to construct SQL statements on-the-fly, which is usually called "dynamic SQL".