July 16, 2008 at 12:41 pm
The below code *should* work, but it is not altering the column datatype.
It does add the Display column if it's not there.
Any ideas???
DECLARE @GFound int,
@SFound int,
@sql nvarchar(max)
SET @sql = '';
SET @GFound = (select count(*) from information_schema.columns where table_catalog = 'Rabbit'
and table_name = 'ptNotes' and column_name = 'Display')
IF(@GFound <= 0) SET @sql = @sql + 'ALTER TABLE [dbo].[ptNotes] ADD [Display] [tinyint] NULL;'
SET @SFound = (select count(*) from information_schema.columns where table_catalog = 'Rabbit'
and table_name = 'ptNotes' and column_name = 'Type')
IF(@SFound <= 0) SET @sql = @sql + 'ALTER TABLE [dbo].[ptNotes] ALTER COLUMN [Type] varchar(10) NULL;'
SET @sql = @sql + 'UPDATE ptNotes Set Type=RTRIM(Type);'
IF(LEN(@sql) >0)
BEGIN
EXEC SP_ExecuteSQL @sql
END
July 16, 2008 at 1:03 pm
The second IF checks if the column exists, and only runs if it doesn't. How is code supposed to alter a non-existent column? Change the comparison to > 0 and you should be fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2008 at 1:19 pm
try using this for the second test:
IF(@SFound = 1) SET @sql = @sql + 'ALTER TABLE [dbo].[ptNotes] ALTER COLUMN [Type] varchar(10) NULL;'
We've all had this kind of experience 😉
Ask someone else and the find this realy silly error .... :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 16, 2008 at 1:31 pm
Additionally, the SQL has "table_catalog = 'Rabbit'", but with SQL Server, the column "table_catalog" means the database name.
What needs to be included is the table_schema (dbo) but not the catalog can be omitted as the scope is only the current database.
Also, when a column is converted from char to varchar, trailing spaces are automatically removed, so there is no need to update rtrim.
First, create the table:
use tempdb
go
IF OBJECT_ID('dbo.ptNotes') is not null drop table [dbo].[ptNotes]
go
create table [dbo].[ptNotes]
(ptNotes_idinteger not null
,typechar(30)null
)
go
Alter the table depending on table columns:
DECLARE @sql nvarchar(max)
SET @sql = '';
IF NOT EXISTS
(select 1
frominformation_schema.columns
wheretable_schema = 'dbo'
andtable_name = 'ptNotes'
andcolumn_name = 'Display')
BEGIN
SET @sql = @sql + 'ALTER TABLE [dbo].[ptNotes] ADD [Display] [tinyint] NULL;'
END
IF EXISTS
(select 1
from information_schema.columns
wheretable_schema = 'dbo'
andtable_name = 'ptNotes'
andcolumn_name = 'Type')
BEGIN
SET @sql = @sql + 'ALTER TABLE [dbo].[ptNotes] ALTER COLUMN [Type] varchar(10) NULL;'
END
ELSE
BEGIN
SET @sql = @sql + 'ALTER TABLE [dbo].[ptNotes] ADD COLUMN [Type] varchar(10) NULL;'
END
IFLEN(@sql) >0
BEGIN
EXEC dbo.sp_executesql @sql
END
View the columns:
select *
frominformation_schema.columns
wheretable_schema = 'dbo'
andtable_name = 'ptNotes'
SQL = Scarcely Qualifies as a Language
July 16, 2008 at 1:44 pm
Good notice Carl.
Another remark:
Keep in mind one should also join with information_schema.tables to find out what kind of table it is. (should be "base table" in this case)
information_schema.columns returns info of columns of tables, view, sprocs, functions, ... !!
Information_schema.tables return tables and views
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 16, 2008 at 1:44 pm
So, do I look funny with egg on my face? Thanks a lot, you just saved my behind!
July 16, 2008 at 1:45 pm
it's the yolk that does it :w00t: 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply