Use of parameters in CREATE and DROP table commands

[PBS2]

I am trying to write a stored procedure in SQL2000 which will be called from an ASP.NET webpage. The procedure needs to create a table but as the name of the table varies I need to pass it as a parameter. The code I am trying to use is as follows:-

CREATE PROCEDURE XX_SamTest
@UserCode char(52), @SelectString char(1024)
AS

IF OBJECT_ID(@UserCode) IS NOT NULL

DROP TABLE @UserCode

CREATE TABLE @UserCode (
[CodeId] [uniqueidentifier]...............)

but the DROP and CREATE TABLE commands get rejected with a syntax error.

Can anyone advise on the correct syntax for this? Many thanks.

 

[DrSql]

Does it work in QA? Is the vlaue that passed from the web is stored ina table or in a fly you are passing to sp. verify the use have dbo previllages.

Dr.Sql
Good Luck.

 

[mrdenny]

You'll need to use dynamic SQL to do this. Something like this.

declare @t1 varchar(255)
set @t1 = 'test'
exec ('drop table ' + @t1)

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief