Check Columns Exist Before Select
-
I'm running into a little problem where I'm trying to run a query on an existing table, but the column I want to check maybe "new" to the table structure
I've only just added some "delete" functionality, so most of the existing tables won't have the new columns
Here's my SQL, which I believe SHOULD alter the table before the main SELECT runs, but it seems to break as it's getting to the SELECT and not finding the new column
set nocount on
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETED' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETED bit;
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETEDBY' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETEDBY int;
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETEDDATE' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETEDDATE datetime;
set nocount off
select Count(*) as Counted
from REGISTRATION
where RG_ENTRYDELETED is null
Any ideas on how to solve this?
I've tried putting a BEGIN TRANSACTION and COMMIT around the IF's, but that makes no difference
Thanks in advance
-
Would it not be better to go through all the tables and add the column?
This will save SQL the time and trouble (Read: Overhead) of having to work it out?
How many tables are there? If there are lots, you could write a script wich will alter all of them by adding a column and a default value.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
My problem is that this procedure is only run during the "administration" part of a website to check how many active records there are, and then give the option to "delete" the records
I don't use the fields anywhere else, so checking/updating them centrally might add an over-head to the users who would never use this functionality
Also, since it's over several databases, each one has to be checked when running the admin part, and only then there maybe new tables
This is why I wanted it to check only when it needs it...
-
Insert GO between your statements,as in
set nocount on
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETED' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETED bit
GO
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETEDBY' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETEDBY int
GO
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETEDDATE' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETEDDATE datetime
GO
set nocount off
select Count(*) as Counted
from REGISTRATION
where RG_ENTRYDELETED is null
Far away is close at hand in the images of elsewhere.
Anon. -
I would love to do that, but I'm using ColdFusion and CFQUERY, and if I use GO it "spits"
Maybe I should turn it into a Stored Procedure and pass it the names of the table and fields... that'd work with the GO's, right?
-
Oops, this is my other login, using a different email address
Forgot I was logged in using this one
-
Unfortunately not. The use of GO splits queries into batches and a proc can only be one batch. BOL states that 'A table cannot be altered and then the new columns referenced in the same batch'. Therefore I think you will not be able to do what you want unless you can split the job in two with two separate calls.
Far away is close at hand in the images of elsewhere.
Anon. -
As a side note, columns of type BIT cannot be null; you can only add one without a default value if the table is empty (at least in my experience). Thus, your select would be broken under the best of circumstances.
Unless, of course, I am wrong, in which case I'll be thrilled to find out whatever I've missed thus far, as always on this site.
RD Francis
R David Francis -
quote:
As a side note, columns of type BIT cannot be null; you can only add one without a default value if the table is empty (at least in my experience). Thus, your select would be broken under the best of circumstances.Unless, of course, I am wrong, in which case I'll be thrilled to find out whatever I've missed thus far, as always on this site.
A column of type BIT can have 3 states;
0, 1 and NULL
NULL is just not entered yet...
Then again, since checking for NULL takes a smidge longer (yes, that's a technical term
), it would be more advantageous to have a default of 0, not allow NULLs, and check for it being 0 -
you could try this
set nocount on
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETED' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETED bit;
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETEDBY' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETEDBY int;
if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETEDDATE' and table_name='REGISTRATION')
alter table REGISTRATION add RG_ENTRYDELETEDDATE datetime;
set nocount off
declare @sql varchar(8000)
select @sql = 'select Count(*) as Counted
from REGISTRATION
where RG_ENTRYDELETED is null '
EXEC (@sql)
-
That works PERFECTLY
I tried using EXEC() on the ALTER TABLE, but not on the SELECT
It takes around half a second when it's doing an update, but that's acceptible as it only ever does it once (per table)
Thank you
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply