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...
>
>

Help wanted msaccess PIVOT-query --> MS-SQL view/sp

Can someone help me parsing this ms-access PIVOT sql-statement to a
ms-sql-server sql-statement?
Many thanks in advance

TRANSFORM Count(KlantenStops.id) AS AantalVanid
SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
FROM KlantenStops
GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
PIVOT DatePart("m",leverdatum,1,0) In
("1","2","3","4","5","6","7","8","9","10","11","12");On Wed, 18 Jan 2006 19:49:49 +0100, Gert v O wrote:

>Can someone help me parsing this ms-access PIVOT sql-statement to a
>ms-sql-server sql-statement?
>Many thanks in advance
>TRANSFORM Count(KlantenStops.id) AS AantalVanid
>SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
>FROM KlantenStops
>GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
>PIVOT DatePart("m",leverdatum,1,0) In
>("1","2","3","4","5","6","7","8","9","10","11","12");

Hi Gert,

I'm not exactly sure how the Access PIVOT syntax works (including table
structure, sample data and expected results would have been a good idea;
check www.aspfaq.com/2006 for the best format to supply this info), but
the query below will do what I think the Access format does:

SELECT Uitvoerder, Klant,
COUNT(CASE WHEN DATEPART(month, leverdatum) = 1 THEN 'TelMe'
END) AS Januari,
COUNT(CASE WHEN DATEPART(month, leverdatum) = 2 THEN 'TelMe'
END) AS Februari,
....,
COUNT(CASE WHEN DATEPART(month, leverdatum) = 12 THEN 'TelMe'
END) AS December
FROM KlantenStops
GROUP BY Uitvoerder, Klant

If you're using SQL Server 2005, you can also use the new PIVOT syntax.
You'll have to check Books Online for the details, though, as I haven't
had a chance to play with the new syntax yet.

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Wed, 18 Jan 2006 19:49:49 +0100, Gert v O wrote:
>> Can someone help me parsing this ms-access PIVOT sql-statement to a
>> ms-sql-server sql-statement?
>> Many thanks in advance
>>
>> TRANSFORM Count(KlantenStops.id) AS AantalVanid
>> SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
>> FROM KlantenStops
>> GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
>> PIVOT DatePart("m",leverdatum,1,0) In
>> ("1","2","3","4","5","6","7","8","9","10","11","12");
>>
> Hi Gert,
> I'm not exactly sure how the Access PIVOT syntax works (including
> table structure, sample data and expected results would have been a
> good idea; check www.aspfaq.com/2006 for the best format to supply
> this info), but the query below will do what I think the Access
> format does:
> SELECT Uitvoerder, Klant,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 1 THEN 'TelMe'
> END) AS Januari,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 2 THEN 'TelMe'
> END) AS Februari,
> ....,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 12 THEN 'TelMe'
> END) AS December
> FROM KlantenStops
> GROUP BY Uitvoerder, Klant
> If you're using SQL Server 2005, you can also use the new PIVOT
> syntax. You'll have to check Books Online for the details, though, as
> I haven't had a chance to play with the new syntax yet.

Thanx Hugo|||I have a blog post on using dynamic SQL to pivot when you don't know how many columns there will be it's here http://www.daymap.net/blog