creating column name from a variable
-
I'm sure this is an easy question but I can't figure it out. I want to dynamically create columns in a table from parameters I feed in but it isn't working
The simple version of what I'm doing would be ...
declare
@thisquarter varchar(10)
declare @mycolumnheader varchar(50)
set
@thisquarter = '2006SU'
set
@mycolumnheader = 'TOT_' + @thisterm
alter
table mytable
add
@mycolumnheader varchar(50)
So from this example, I'd like a column added that is named Tot_2006SU. Thanks so much for any help.
-
I'm a little worried here... Why exactly do you need to modify a table at run time without first knowing the column name??
-
It looks like a denormalized reporting or summary table that they will add a column to for every quarter...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Looks like or is Jeff
? -
And it may be wise to use IF EXISTS or IF NOT EXISTS script to check if the column is already existing on the table just to avoid errors.
Prasad Bhogadi
www.inforaise.com -
Considering that the original poster had troubles with the dynamic SQL to create the column, you might want to demo how to test for the column's existance with some generic code, as well
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
DECLARE @thisquarter VARCHAR(10)
DECLARE @sql NVARCHAR(4000)
SET @thisquarter = '2006SU'
SET @sql = 'IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=object_id(''mytable'') and name=''TOT_' + @thisquarter + ''')
ALTER TABLE mytable
ADD TOT_' + @thisquarter + ' VARCHAR(50) '
EXEC(@SQL)
Prasad Bhogadi
www.inforaise.com -
Replace "Sys." to "dbo." if you are using 2000 or older.
IF
NOT EXISTS (Select * from Sys.SysColumns where Name = 'Colname' AND id = Object_id('TableName'))
begin
--create column
end
-
Both methods are good... so long as you are in the correct data base and the table actually exists in that data base...
if it doesn't, the code will not find the column name (no table to find it in) and the logic will allow the Dynamic ALTER
to be executed and BOOM!
Of course, you can get around this by checking for the EXISTS condition anddoing the ALTER as part of the ELSE, but read on...
Also, both methods use system tables which seems to have fallen out of favor with DBAs and Microsoft alike. Since
I don't personally care for the Information Schema views (no practical reason, I just prefer system functions over
views in this case), here's a general purpose UDF that should work in any database...
CREATE FUNCTION dbo.CheckColExists /*********************************************************************************************** This function accepts a tablename or a username.tablename and a column name. It first tests to make sure the table exists in the current database and then the column.
Returns 'Table Missing' -- The table does not exist in the current database. 'No' -- The table exists but the column does not. 'Yes' -- Both the table and the column exist. 'Unknown' -- Catch all... one of the 3 conditions above could not be determined. ***********************************************************************************************/ --===== Declare the I/O parameters ( @pTableName SYSNAME, @pColumnName SYSNAME ) RETURNS VARCHAR(13) AS BEGIN --Body of function RETURN (SELECT CASE --== Check if table exists WHEN OBJECT_ID(@pTableName) IS NULL THEN 'Table Missing' --== Table exists, check if column exists WHEN COLUMNPROPERTY(OBJECT_ID(@pTableName),@pColumnName,'Precision') IS NULL THEN 'No' WHEN COLUMNPROPERTY(OBJECT_ID(@pTableName),@pColumnName,'Precision') IS NOT NULL THEN 'Yes' --== Something else happened ELSE 'Unknown' END ) END --Body of function
From there, the check is simple... and everyone who has code that adds columns uses the same code with the same pre-check for the table... no surprises...
You could even modify it a bit to
accept DEFAULT for the column name and use it as a check for table existance...DECLARE @TableName SYSNAME
DECLARE @ColumnName SYSNAME
DECLARE @DynSQL VARCHAR(8000)
SET @pTableName = 'MyTable'
SET @pColumnName = 'TOT_2006SU'
IF dbo.CheckColExists(@TableName,@ColumnName) = 'No'
BEGIN
SET @DynSQL = 'ALTER TABLE ' + @TableName
+ ' ADD COLUMN ' + @ColumnName + 'VARCHAR(50)'
EXEC (@DynSQL)
END
ELSE
BEGIN
...put something here to handle the exception...
...you can even include the 'Table Missing' condition...
...or whatever...
END
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. Just to save face... if this is NOT a simple denormalized reporting table, you need to change my statement from...
"and everyone who has code that adds columns uses the same code with the same pre-check for the table"
...to...
"and everyone who uses this code should be shot butt first out of a long cannon into a stone wall".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Did anybody appreciate the fact I kept silence about this topic?
_____________
Code for TallyGenerator -
Is it because you don't appreciate using dynamic sql !!! Also I have a question with respect to using BEGIN and END in a SQL where there is no Transaction defined.
Prasad Bhogadi
www.inforaise.com -
I agree about the BEGIN/END where no transaction is defined... however, if you check out Books Online, you'll find that BEGIN/END is required syntax in UDF's
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply