Cursor

I have a table with the following data:
14794 3 Trond
14794 4 has
14794 5 new car
Here is the table:
CREATE TABLE [IText] (
[FKID] [int] NOT NULL ,
[SortIndex] [int] NOT NULL ,
[Content] [ntext] COLLATE Danish_Norwegian_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
I then want to create a stored procedure that takes a parameter and returns
the ID (FKID) and a concantinated string "Trond has a new car". This because
all 3 records has the same FKID (14794)
Here is the sproc so far:
CREATE PROCEDURE testSPROC @.ID int
AS
DECLARE @.Concantinated varchar(500)
DECLARE @.ConcantinatedFinal varchar(500)
DECLARE @.FKID int
DECLARE Content_cursor CURSOR for
SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
OPEN Content_cursor
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
END
CLOSE Content_cursor
deallocate Content_cursor
SELECT @.FKID as FKID, @.ConcantinatedFinal as ConString
GO
The thing is that ConcantinatedFinal is returning nothing (NULL). If i
change SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated to
SET @.ConcantinatedFinal = @.Concantinated it will return "new car"
I am so lost on this one. Can anyone see what i am doing wrong?
I am running this on a SQL 2000 server
best regards
TrondYou have to initialize the variable before using it in an operation (if not,
it is null).
...
DECLARE @.ConcantinatedFinal varchar(500)
DECLARE @.FKID int
set @.ConcantinatedFinal = ''
...
AMB
"Trond" wrote:

> I have a table with the following data:
> 14794 3 Trond
> 14794 4 has
> 14794 5 new car
> Here is the table:
> CREATE TABLE [IText] (
> [FKID] [int] NOT NULL ,
> [SortIndex] [int] NOT NULL ,
> [Content] [ntext] COLLATE Danish_Norwegian_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> END
> I then want to create a stored procedure that takes a parameter and return
s
> the ID (FKID) and a concantinated string "Trond has a new car". This becau
se
> all 3 records has the same FKID (14794)
>
> Here is the sproc so far:
> CREATE PROCEDURE testSPROC @.ID int
> AS
> DECLARE @.Concantinated varchar(500)
> DECLARE @.ConcantinatedFinal varchar(500)
> DECLARE @.FKID int
> DECLARE Content_cursor CURSOR for
> SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
> OPEN Content_cursor
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> END
> CLOSE Content_cursor
> deallocate Content_cursor
> SELECT @.FKID as FKID, @.ConcantinatedFinal as ConString
> GO
>
> The thing is that ConcantinatedFinal is returning nothing (NULL). If i
> change SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
to
> SET @.ConcantinatedFinal = @.Concantinated it will return "new car"
> I am so lost on this one. Can anyone see what i am doing wrong?
> I am running this on a SQL 2000 server
> best regards
> Trond
>
>|||Thank you. It works fine now thanks to you. Feel a lil stupid since i did
not see that one myself, but sometimes that just one of those things that
happends in a developers life :-)
Final SPROC:
----
--
CREATE PROCEDURE testSPROC @.ID int
AS
DECLARE @.Concantinated varchar(500)
DECLARE @.ConcantinatedFinal varchar(500)
DECLARE @.FKID int
set @.ConcantinatedFinal = ''
DECLARE Content_cursor CURSOR for
SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
OPEN Content_cursor
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
END
CLOSE Content_cursor
deallocate Content_cursor
SELECT @.FKID as FKID, RTRIM(LTRIM(@.ConcantinatedFinal)) as ConString
GO
----
--
Best regards
Trond
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EFAD8EAC-69F4-4E8A-AB97-148A43798BAB@.microsoft.com...
> You have to initialize the variable before using it in an operation (if
not,
> it is null).
> ...
> DECLARE @.ConcantinatedFinal varchar(500)
> DECLARE @.FKID int
> set @.ConcantinatedFinal = ''
> ...
>
> AMB
> "Trond" wrote:
>
returns
because
+@.Concantinated to|||Do not feel bad, I have been there too.
AMB
"Trond" wrote:

> Thank you. It works fine now thanks to you. Feel a lil stupid since i did
> not see that one myself, but sometimes that just one of those things that
> happends in a developers life :-)
> Final SPROC:
> ----
--
> --
> CREATE PROCEDURE testSPROC @.ID int
> AS
> DECLARE @.Concantinated varchar(500)
> DECLARE @.ConcantinatedFinal varchar(500)
> DECLARE @.FKID int
> set @.ConcantinatedFinal = ''
>
> DECLARE Content_cursor CURSOR for
> SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
> OPEN Content_cursor
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> END
> CLOSE Content_cursor
> deallocate Content_cursor
> SELECT @.FKID as FKID, RTRIM(LTRIM(@.ConcantinatedFinal)) as ConString
> GO
> ----
--
> --
> Best regards
> Trond
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:EFAD8EAC-69F4-4E8A-AB97-148A43798BAB@.microsoft.com...
> not,
> returns
> because
> +@.Concantinated to
>
>|||(oops, fast fingers in a web-based UI)
DECLARE Content_Cursor LOCAL FAST_FORWARD FOR SELECT ...
OPEN Content_Cursor
WHILE 1 = 1 BEGIN
FETCH NEXT FROM Content_Cursor INTO ...
IF @.@.FETCH_STATUS <> 0 BREAK
..
END