wood burning stoves 2.0*
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


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored Procedure problem" Watch "Stored Procedure problem" New topic
Author

Stored Procedure problem

Aadil Pal
Greenhorn

Joined: Feb 23, 2006
Posts: 3
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

Joined: Jan 10, 2002
Posts: 61104
    
  66

"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


[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:
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

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
 
 
subject: Stored Procedure problem