Adding Column to table

I'm writing a procedure to add column to the database but need to check if
the column already exist.
How does one check to see if it exist. The reason is that if I have multiple
statements adding columns then it seem that the procedure will error out
and not go to the second statement. So if rowguid already exits in the table
user, it will not do the next statement.
Thanks for your help
Stephen K. Miyasato
Alter table users ADD [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
CONSTRAINT [DF__Users__rowguid__4C220BCC] DEFAULT (newid())
Alter table users ADD User_Type smallIntHere's one method:
IF COLUMNPROPERTY(OBJECT_ID('users'),'rowgu
id','AllowsNull') IS NULL
ALTER TABLE users ADD rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
CONSTRAINT [df_users_rowguid] DEFAULT (NEWID())
However, I don't recommend you do this in a stored procedure. One problem is
that you will still get errors if you reference the column in static code in
the same proc. That's because the column name has to be resolvable at
compile time and not just when a statement is executed. Another issue is the
disproportionate effort required to test, debug and maintain systems that
modify schema at runtime. Schema mods should happen at install time so I
can't think of many good reasons to do this in a proc.
David Portas
SQL Server MVP
--
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:eJtl9toxFHA.900@.TK2MSFTNGP11.phx.gbl...
> I'm writing a procedure to add column to the database but need to check if
> the column already exist.
> How does one check to see if it exist. The reason is that if I have
> multiple statements adding columns then it seem that the procedure will
> error out and not go to the second statement. So if rowguid already exits
> in the table user, it will not do the next statement.
> Thanks for your help
> Stephen K. Miyasato
>
> Alter table users ADD [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> CONSTRAINT [DF__Users__rowguid__4C220BCC] DEFAULT (newid())
> Alter table users ADD User_Type smallInt
>

Adding column to a table before an existing column

I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.

The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?

Thanks.You can do this easily through Enterprise Manager in the table design form. If you must do this using TSQL, then change your table through Enterprise Manager and instead of saving your changes use the button in the menu bar to script the changes. Copy the code and paste it into your application with whatever modifications you require.|||Why bother?

If the table has volumes of data, it'll take awhile...|||i agree
there is no reason to need to add a column before another column because you can change the order of the columns in the select list of your select statement.

it really doesnt matter where the columns exist.

Adding an Expression to a Report Text Box

I added this expression to the Text Box on the Report and it returns the
Nothing Value, yet the value does exist.
=Iif(Fields!USCATVLS_1.Value = "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing )
If I add:
=(Fields!USCATVLS_1.Value)
the Value "External" returns to the report.
The documentaion indicates this should work.
Pls Help.
--
MickOn Jun 6, 6:54 am, Mick Egan <MickE...@.discussions.microsoft.com>
wrote:
> I added this expression to the Text Box on the Report and it returns the
> Nothing Value, yet the value does exist.
> =Iif(Fields!USCATVLS_1.Value => "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing )
> If I add:
> =(Fields!USCATVLS_1.Value)
> the Value "External" returns to the report.
> The documentaion indicates this should work.
> Pls Help.
> --
> Mick
Are you sure that Fields!BNFITAMT_5.Value is not nothing? The
expression seems to be fine otherwise.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
When I swap places with the "Nothing" value it returns all the values, so
the calculation is valid.
Mick
--
Mick
"EMartinez" wrote:
> On Jun 6, 6:54 am, Mick Egan <MickE...@.discussions.microsoft.com>
> wrote:
> > I added this expression to the Text Box on the Report and it returns the
> > Nothing Value, yet the value does exist.
> >
> > =Iif(Fields!USCATVLS_1.Value => > "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing )
> >
> > If I add:
> > =(Fields!USCATVLS_1.Value)
> > the Value "External" returns to the report.
> >
> > The documentaion indicates this should work.
> > Pls Help.
> > --
> > Mick
>
> Are you sure that Fields!BNFITAMT_5.Value is not nothing? The
> expression seems to be fine otherwise.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
>|||Even though Fields!USCATVLS_1.Value returns "External", or appears to, in
the report, is it possible that there are actually some trailing spaces?
IOW, what if you used the following condition in your expression:
Fields!USCATVLS_1.Value.Trim() = "External"
... or something like that?
>L<
"Mick Egan" <MickEgan@.discussions.microsoft.com> wrote in message
news:25B7A52C-877A-4C7D-9AC9-817A179ECFB0@.microsoft.com...
>I added this expression to the Text Box on the Report and it returns the
> Nothing Value, yet the value does exist.
> =Iif(Fields!USCATVLS_1.Value => "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing
> )
> If I add:
> =(Fields!USCATVLS_1.Value)
> the Value "External" returns to the report.
> The documentaion indicates this should work.
> Pls Help.
> --
> Mick|||Lisa / Enrique,
The .Trim() isn't recogonised, so I tried (Trim(Fields!USCATVLS_1.Value) ="External", this didn't work either.
So I tried:
Setting the RTRIM on the Dataset and this worked, I needed to add the field
as an expression.
i.e. RTRIM(IV00101.USCATVLS_2) AS CAT2
Thanks heaps for pointing me in the right direction.
Mick
Mick
"Lisa Slater Nicholls" wrote:
> Even though Fields!USCATVLS_1.Value returns "External", or appears to, in
> the report, is it possible that there are actually some trailing spaces?
> IOW, what if you used the following condition in your expression:
> Fields!USCATVLS_1.Value.Trim() = "External"
> ... or something like that?
> >L<
> "Mick Egan" <MickEgan@.discussions.microsoft.com> wrote in message
> news:25B7A52C-877A-4C7D-9AC9-817A179ECFB0@.microsoft.com...
> >I added this expression to the Text Box on the Report and it returns the
> > Nothing Value, yet the value does exist.
> >
> > =Iif(Fields!USCATVLS_1.Value => > "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing
> > )
> >
> > If I add:
> > =(Fields!USCATVLS_1.Value)
> > the Value "External" returns to the report.
> >
> > The documentaion indicates this should work.
> > Pls Help.
> > --
> > Mick
>