• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Stored Procedure problem

 
Aadil Pal
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Author and ninkuma
Marshal
Pie
Posts: 65124
91
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In Transact-SQL you can't use a variable in place of an object name. If you wrote:

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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic