This week's book giveaway is in the JDBC forum.
We're giving away four copies of Make it so: Java DB Connections & Transactions and have Marcho Behler on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Stored Procedure problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Make it so: Java DB Connections & Transactions this week in the JDBC forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Stored Procedure problem" Watch "Stored Procedure problem" New topic

Stored Procedure problem

Aadil Pal

Joined: Feb 23, 2006
Posts: 3

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
FROM sysobjects
WHERE name = N'testing'
AND type = 'P')

declare @tablename varchar(50)
set @tablename = 'IT2NETSQL01CHA.PSQL_GCSS0001_DEV.dbo.SEC_LOG'
SELECT * FROM [@tablename]

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 ?

Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63548

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

JavaRanch Sheriff

[Asking smart questions] [About Bear] [Books by Bear]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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:

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

Joined: Apr 14, 2004
Posts: 10336

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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
I agree. Here's the link:
subject: Stored Procedure problem
It's not a secret anymore!