Passing table name in stored procedure got error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing table name in stored procedure got error

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-13 : 00:14:24

ALTER PROCEDURE [dbo].[sp_STATEWLEVEL_DAILY]
@STATE varchar(50),@TBLNAME varchar(50)

AS
BEGIN TRANSACTION -- Start the transaction
TRUNCATE TABLE @TBLNAME;
SELECT
t1.Date_Taken as 'DATE', t1.Time as 'TIME',
t1.Main_ID as 'MAIN_ID', t1.WATER_ULEVEL as 'WATER_ULEVEL'
FROM dbo.SEL t1 INNER JOIN dbo.station_info t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.STATE=@STATE
AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time

-- See if there is an error
IF @@ERROR <> 0
-- There's an error b/c @ERROR is not 0, rollback
ROLLBACK
ELSE
COMMIT -- Success! Commit the transaction

Error said Incorrect syntax near '@TBLNAME'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 00:45:58

You can't do this without dynamic SQL. But why do you need to pass in the table name?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-05-13 : 00:47:01

I think you need dynamic sql for executing TRUNCATE TABLE @TBLNAME.

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-13 : 01:05:45

Mr. tkizer,

I need pass the table name because i've a lot of summary table. If i can pass the table name, it saving time for creating a lot of Stored Procedure.

It is possible to pass the table name via Stored Procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 01:22:02

quote:
Originally posted by wkm1925

Mr. tkizer,

I need pass the table name because i've a lot of summary table. If i can pass the table name, it saving time for creating a lot of Stored Procedure.

It is possible to pass the table name via Stored Procedure?

You can pass table name via stored procedure. Just declare a varchar parameter and use it to pass the value just like you have done above.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-13 : 02:45:35

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-13 : 02:56:51

mr. visakh,

i'll already declare physical tables in database. it that ok if i create table variables (same definition in physcal table) in stored procedure?

still got error Must declare the table variable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-13 : 03:05:46

quote:
Originally posted by wkm1925

mr. visakh,

i'll already declare physical tables in database. it that ok if i create table variables (same definition in physcal table) in stored procedure?

still got error Must declare the table variable

Read the article I posted

Madhivanan

Failing to plan is Planning to fail

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-13 : 03:14:28

Passing table and column names as parameters to a procedure with dynamic SQL is rarely a good idea for application code. (It can make perfectly sense for admin tasks). As I've said, you cannot pass a table or a column name as a parameter to sp_executesql, but you must interpolate it into the SQL string. Still you should protect it against SQL injection, as a matter of routine. It could be that bad it comes from user input.

in my case, if i still want to pass table name, it must use quotename()?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources