Stored Procedure problem

[Aadil Pal]

Hi,

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 ?

Thanks
Aadil

[Bear Bibeault]

"Aadil M",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
JavaRanch Sheriff

[stu derby]

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".

I don't know the SQL server SP language at all, but Googling for 'sql server "stored procedure" dynamic SQL' turns up some likely things:
http://www.sqlteam.com/item.asp?ItemID=4619
http://www.google.com/search?q=sql+server+%22stored+procedure%22+dynamic+SQL

Looks like you need to construct a SQL statement into a variable and then use the Exec() function.

[ May 18, 2006: Message edited by: stu derby ]
[ May 18, 2006: Message edited by: stu derby ]

[Paul Sturrock]

In Transact-SQL you can't use a variable in place of an object name. If you wrote:

CREATE PROCEDURE testing AS
begin
 declare @tablename varchar(50)
 set @tablename = 'IT2NETSQL01CHA.PSQL_GCSS0001_DEV.dbo.SEC_LOG'
 SELECT * FROM @tablename
end

SQL Server would fail to compile the procedure. However because you wrapped the @tablename with brackets, you got away with it.

Follow stu derby's suggestion. He's worked out how to do it.
[ May 18, 2006: Message edited by: Paul Sturrock ]