adding column names using the stored procedure.error in the @ character
-
i was trying to add the column names in the existing table using the stored procedure in sql server 2000. My stored procedure is
CREATE PROCEDURE [dbo].[spInsert_Master] AS declare @les varchar(1000) delete tblmaster create table tblMaster(categoryname varchar(100) , casestudy varchar(100) , UnitInformation varchar(100) , locationname varchar(100)) declare les cursor for select lessonname from tblLessonMaster open les fetch next from les into @les while(@@fetch_status =0) begin fetch next from les into @les alter table tblMaster add @less varchar(500); select @les as lessonname end close les deallocate lesI got the error in the following line alter table tblMaster add @less varchar(500); says error near @less i think i can't add the @ expression in the column name. any idea for the alternative ? I want to add the column names in the existing table using stored procedure.If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
-
i was trying to add the column names in the existing table using the stored procedure in sql server 2000. My stored procedure is
CREATE PROCEDURE [dbo].[spInsert_Master] AS declare @les varchar(1000) delete tblmaster create table tblMaster(categoryname varchar(100) , casestudy varchar(100) , UnitInformation varchar(100) , locationname varchar(100)) declare les cursor for select lessonname from tblLessonMaster open les fetch next from les into @les while(@@fetch_status =0) begin fetch next from les into @les alter table tblMaster add @less varchar(500); select @les as lessonname end close les deallocate lesI got the error in the following line alter table tblMaster add @less varchar(500); says error near @less i think i can't add the @ expression in the column name. any idea for the alternative ? I want to add the column names in the existing table using stored procedure.If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
-
try replacing this line
alter table tblMaster add @less varchar(500);withSET @Str = 'alter table tblMaster add ' + @less ' varchar(500);' Exec (@Str)also need to declare @Str variable at the beginingRegards KP
how can i check duplicate column names exist or not in the table?
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
-
how can i check duplicate column names exist or not in the table?
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
in SQL 2000 syscolumns contains all column names of each table and sysobjects contains all table names Value 'U' in the column sysobjects.xtype indicates the objects is user table id column in both tables are common which to be used for joining the two tables.
Regards KP
-
in SQL 2000 syscolumns contains all column names of each table and sysobjects contains all table names Value 'U' in the column sysobjects.xtype indicates the objects is user table id column in both tables are common which to be used for joining the two tables.
Regards KP
can u tell me a query which gives me the result regarding ,whether the particular column name already exist or not in the table?
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
-
can u tell me a query which gives me the result regarding ,whether the particular column name already exist or not in the table?
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
Assuming SQL Server 2000
if exists (select 1 from syscolumns where name = 'yourcolumnname' and id = object_id('yourtablename') begin select 'It exists' end else begin select 'its new' endthis will also work with SQL Server 2005 but you should be using the sys.xxx viewsBob Ashfield Consultants Ltd