table name
-
Hi, everybody
I want to create a table name but the table's name must use a parameter
for example
declare @ca varchar(20)
declare @fechai datetime
declare @personaltablename varchar (256)
set @fechai = dateadd(mm,-1,getdate())
set @ca = convert(varchar, @fechai, 111)
set @ca = substring(@ca,1,8) + '01'
set @personaltablename ='transof' + cast(year(@ca) as varchar) + cast(month(@ca) as varchar)
CREATE TABLE @personaltablename (
[field1] [int] IDENTITY (1, 1) NOT NULL ,
[field2] [int] NOT NULL DEFAULT (0),
[field3] [int] NOT NULL
)
GO
what's wrong and how can i generate corectly a table name
Note: I push the variable @personaltablename in brackets []
Thanks in advance.
-
You need to build the create table statement in dynamic SQL
declare @ca varchar(20)
declare @fechai datetime
declare @personaltablename varchar (256)
declare @sql varchar(5000)
set @fechai = dateadd(mm,-1,getdate())
set @ca = convert(varchar, @fechai, 111)
set @ca = substring(@ca,1,8) + '01'
set @personaltablename ='transof' + cast(year(@ca) as varchar) + cast(month(@ca) as varchar)
set @sql =
'CREATE TABLE '+@personaltablename+' (
[field1] [int] IDENTITY (1, 1) NOT NULL ,
[field2] [int] NOT NULL DEFAULT (0),
[field3] [int] NOT NULL
)'
exec (@sql)
-
Or
- set
nocount on set ansi_warnings off
- go
- if exists(select * from sysobjects where xtype = 'U' and name = 'temptablename_forproc1432' and uid=1)
- drop
table dbo.temptablename_forproc1432
- go
- create
table dbo.temptablename_forproc1432( field1 int identity not null,
- field2 int not null default (0), field3 int not null)
- go
- declare
@fechai datetime, @personaltablename sysname
- select @fechai = dateadd(mm,-1,getdate()), @personaltablename ='transof' + cast(((year(@fechai)*100) + month(@fechai)) as char(6))
- exec
sp_rename N'dbo.temptablename_forproc1432', @personaltablename
- go
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Couple of years ago one guy asked the author of the same king of question about he name of conpany he's working for. To aviod using products of that company ander any circumstanses.
Seems we've got flood of not bad, but terrible approaches made by people not qualified to type even one row in T-SQL.
Microsoft sentenced SQL Server to death by issuing SQL Server 2005.
This thing invites developer into database development, makes them think there is no much difference, just another syntax.
In a year or two almost every system built on SQL Server gonna be slow, non scalable, non manageble, not reliable. It will become next MS Access - home application database.
MS Access is not acceptable for commercial application, so MS SQL Server goes the same way.
Not because of the quality of the product but because of the quality of programmers allowed to develop on this platform.
_____________
Code for TallyGenerator -
What is the revlevance of your post, Sergei?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Sorry I misspelt your name Sergiy. Still I'm afraid your dark mutterings about procedural programmers (not me) don't cut any ice unless backed with some sort of substantive criticism.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
There is nothing inherently procedural about issuing dynamic ddl commands.
Having generic, automated methods of building relational structures should only increase the relational solidity of sql dbs by cutting out human error and instantiating a 'best practise' standard within it.
All the code in this thread is sql 2000 compatible.
Relying on sys sps like sp_rename is arguably more flaky than using ansi standard ddl
...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply