Stored procedure error [SQL Server 2008 Express]

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

Stored procedure error [SQL Server 2008 Express]

Author

Topic

Largo
Starting Member

22 Posts

Posted - 2009-09-23 : 03:42:21

I have the following stored procedure:



CREATE PROCEDURE usp_DeleteTable
	@tblname AS NVARCHAR(200)
AS
	IF OBJECT_ID(@tblname, 'U') IS NOT NULL
		DROP TABLE OBJECT_NAME(OBJECT_ID(@tblname,'U'))

However, SQL Server produces error: Incorrect syntax near 'OBJECT_ID'. I checked data types of all parameters - no casting is required. OBJECT_ID returns integer, while OBJECT_NAME expects integer. What is wrong?

-----
There is no knowledge that is not power.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 03:56:21

[code]CREATE PROCEDURE usp_DeleteTable
@tblname AS NVARCHAR(200)
AS
declare @sql varchar(1000)
set @sql=''
IF OBJECT_ID(@tblname, 'U') IS NOT NULL
set @sql=@sql+'DROP TABLE '+@tblname
exec(@sql)
[/code]

Madhivanan

Failing to plan is Planning to fail