help with a 'simple' sql-statement

Hi there,
i have the following problem: in a database i have to detect if a certain
column in a certain table exists. I
use the function 'ColumnAlreadyExists'. If not i have to create that column
and then i have to fill this
column. This is my code:
****************************************
*********
-- Function to check whether a specific column exists in a table
CREATE FUNCTION ColumnAlreadyExists(@.TableName NVARCHAR(128),@.ColumnName
NVARCHAR(128))
RETURNS INTEGER --Returns 0 if column does not exist. Returns 1 if column
exists.
AS
BEGIN
--See if the Table already contains the column.
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name=@.TableName
AND C.Name=@.ColumnName)
RETURN 1
--Table does not contain the column.
RETURN 0
END
GO
-- Add column DataId to table Data if necessary
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill column DataId for each row in the Data table
DECLARE @.index1 int, @.index2 int, @.datapk int
DECLARE DataID_Cursor CURSOR FOR
SELECT index1, index2, datapk
FROM data
OPEN DataID_Cursor
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE Data
set dataid = ( index1 * index2 )
WHERE datapk = @.datapk
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
END
END
DROP FUNCTION ColumnAlreadyExists
****************************************
*********
The problem now is that when i want to fill the dataid column it does not
exists yet, cause there was no GO
yet. But when i put a GO between the creating of the column and the filling
of this column i don't know
anymore whether the column existed or not. So my question is: How can i do
the following:
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill the just created column dataid for each row in the Data table
END
Can anyone help me with this problem?
thanks,
Koert"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...

> Can anyone help me with this problem?
Maybe DECLARE a variable...?|||I already tried that but I can't use that variable after the GO to create th
e
column. I tried:
DECLARE @.result int
SELECT @.result = .dbo.ColumnAlreadyExists('data','dataid')
IF @.result = 0
BEGIN
ALTER TABLE [dta]
ADD [dataid][int] NULL
END
GO
if @.result = 0 <--This one is not declared anymore
BEGIN
--Fill column DataID
END
so this does not work or is there any other way to declare a kind of global
variable'
thanks,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...
>
> Maybe DECLARE a variable...?
>
>|||"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...

> so this does not work or is there any other way to declare a kind of
> global
> variable'
Hmm - loathe though I am to suggest it, I think your only option might be to
create a temporary table... Temporary tables are persistent within a
connection, so should survive the GO statement...|||i thought about that to, it seemed to be so easy... The only other way i can
think of is to check if the dataid column is filled in, if not fill it.
thanks for posting,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...
>
> Hmm - loathe though I am to suggest it, I think your only option might be
to
> create a temporary table... Temporary tables are persistent within a
> connection, so should survive the GO statement...
>
>