Create a parametised Stored Proc?

[LucieLastic]

hi All

I'm using Sybase so I'm hoping the syntax will be the same, but I can't get the following to work - Can anyone shed any light?

CREATE PROC TruncateTables (@TableName varchar(35))
AS
TRUNCATE TABLE @TableName
RETURN

error:
Server Message: Number 102, Severity 15
Procedure 'TruncateTables', Line 3:
Incorrect syntax near '@TableName'.

many thanks for any help
lou

 

[nguyentaiuyenchi]

Hi

I don't know Sybase, however if it is similar with SQL, I think you should change the code as follows:

CREATE PROC TruncateTables (@TableName varchar(35))
AS
DECLARE @strSql varchar(500)
SET @strSql='TRUNCATE TABLE ' + @TableName
EXEC @strSql
RETURN

I hope it is useful for you.


Uyen Chi
Software developer

 

[LucieLastic]

Thankyou Uyen, I'll try that.

lou

 

[tlbroadbent]

You need parentheses around the Exec argument.

EXEC (@strSql)

You should post in forum187 - Sybase: Adaptive (SQL) Server.


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ChainsawJoe]

or just;

CREATE PROC TruncateTables (@TableName varchar(35))
AS
EXEC ('TRUNCATE TABLE ' + @TableName)
RETURN

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------

 

[LucieLastic]

Thanks for the replies.

Terry, I've tried posting in that forum before but it doesn't have many contributors so one may not get a reply for about a week.

I like this forum and I understand that SQLServer and Sybase used to be the same product a few moons ago so thought I'd post on here as there are alot of similiarites between them still.

lou