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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-07-17 : 07:39:03
|
| Joe writes "Hi,Environment: SQL Server 2000, Windows 2000I am trying to drop and create a table with in a stored procedure that takes TableName as parameter. I could successfully do the create table part based on the parameter. Here is the SQL I am using.CREATE PROCEDURE sp_CreateTable ( @TableName char (50) )AS SET NOCOUNT ON DECLARE @SqlString nVARCHAR(2000) set @SqlString = ''/*NOTE: My problem is I am not able to embed the parameter in the following string. Help me with the syntax for substituting this parameter in the following string please.*/ set @SqlString = @SqlString + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[@TableName] set @SqlString = @SqlString + 'CREATE TABLE [dbo].[' + rtrim(@TableName) + '] (' set @SqlString = @SqlString + '[NewVehScheduleId] [int] IDENTITY (1, 1) NOT NULL,' set @SqlString = @SqlString + '[WorkSpaceId] [int] NULL,' set @SqlString = @SqlString + '[NewVehInvoiceId] [int] NULL,' set @SqlString = @SqlString + '[ScheduleStockNo] [varchar] (18) NULL,' set @SqlString = @SqlString + '[ScheduleAmt] [money] NULL,' set @SqlString = @SqlString + '[WriteUp] [money] NULL,' set @SqlString = @SqlString + '[WriteDown] [money] NULL,' set @SqlString = @SqlString + '[Pack] [money] NULL,' set @SqlString = @SqlString + '[NonInventoryItem] [int] NULL,' set @SqlString = @SqlString + '[HeavyTruck] [int] NULL,' set @SqlString = @SqlString + '[SchedDescription] [text] NULL,' set @SqlString = @SqlString + '[Note] [text] NULL,' set @SqlString = @SqlString + '[Intransit] [int] NULL,' set @SqlString = @SqlString + '[HoldBack] [money] NULL,' set @SqlString = @SqlString + '[AdvertisingAmt] [money] NULL,' set @SqlString = @SqlString + '[FloorPlanAmt] [money] NULL,' set @SqlString = @SqlString + '[Other] [money] NULL)' Execute(@SqlString)GO" |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-07-17 : 08:12:50
|
| I don't understand why everyone insist on doing this kind of thing... But ok... Here goes:set @SqlString = @SqlString + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].['+@TableName+']) and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table [dbo].['+@TableName+']' |
 |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-07-17 : 08:27:24
|
| 'if exists (select * from sysobjects where name =''' + @TableName +''' and xtype=''U'') drop table ' + @TableName + ' '- Vit |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-17 : 08:56:19
|
I dont know what is more troubling, the fact that you gonna have multiple tables with the exact same structure...or that you create them so often that you need to have a procedure for it?  |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-18 : 04:19:29
|
| Gah. People should be banned from doing this sort of thing. It's a Bad Plan.What if someone was to dosp_CreateTable 'yourreallyusefulproductiontable':/-------Moo. :) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-18 : 07:42:32
|
| Joe, consider adding or deleting ROWS from a SINGLE table, all flagged with a column that designates which "table" the data belongs to.THen you don't need dynamic SQL, your data is in one table, no objects are removed or created, and you are using good database design.Also, consider adding a primary key to this table !! you don't have one in your definition. I assume it is the NewVehScheduleId column ???- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Drop table with TableName as a parameter