permaculture playing cards*
The moose likes JDBC and the fly likes Stored Procedure problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
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: 60781

"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] [Bear's FrontMan] [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
Similar Threads
Create procedure for inserting record into table : Table name dynamically as an Param
Getting result set using callablestatement??
retrieving value from SQL Server 2000 stored procedure
Create procedure for inserting record into table : Table name dynamically as an Param
Retrieiving OUT parameters & return value from a Sybase Stored Procedure