variable always empty in stored procedure

[Daniel Fisherman]

In the code below, the statement 'Print @Sites' prints nothing, even
though the cursor contains 4 records, and 'Print @Site' prints the
appropriate values. Can anyone see my mistake? I am attempting to
print a delimited string of the values referred to by @Sites.

Thanks.
Dan Fisherman


DECLARE SiteCursor CURSOR
GLOBAL
SCROLL
STATIC
FOR SELECT OfficeName FROM ClientOffices WHERE ClientID=12 ORDER BY
OfficeName

OPEN SiteCursor

DECLARE @Sites varchar(1000)
DECLARE @Site varchar(100)

FETCH NEXT FROM SiteCursor INTO @Site

WHILE @@FETCH_STATUS=0
BEGIN
print @Site
SET @Sites = @Sites + ', ' + @Site
FETCH NEXT FROM SiteCursor INTO @Site
END

PRINT @Sites
CLOSE SiteCursor
DEALLOCATE SiteCursor
GO

[Shervin Shapourian]

Daniel,

The initial value of @Sites is null, so @Sites + ', ' + @Site is always
null.
Set it to blank before your loop.

Set @Sites = ''

Shervin

"Daniel Fisherman" <df********@optonline.net> wrote in message
news:a9*************************@posting.google.co m...

In the code below, the statement 'Print @Sites' prints nothing, even
though the cursor contains 4 records, and 'Print @Site' prints the
appropriate values. Can anyone see my mistake? I am attempting to
print a delimited string of the values referred to by @Sites.

Thanks.
Dan Fisherman


DECLARE SiteCursor CURSOR
GLOBAL
SCROLL
STATIC
FOR SELECT OfficeName FROM ClientOffices WHERE ClientID=12 ORDER BY
OfficeName

OPEN SiteCursor

DECLARE @Sites varchar(1000)
DECLARE @Site varchar(100)

FETCH NEXT FROM SiteCursor INTO @Site

WHILE @@FETCH_STATUS=0
BEGIN
print @Site
SET @Sites = @Sites + ', ' + @Site
FETCH NEXT FROM SiteCursor INTO @Site
END

PRINT @Sites
CLOSE SiteCursor
DEALLOCATE SiteCursor
GO

[Daniel Fisherman]

The following code still prints an empty line when executing 'Print
@Sites'. Any ideas?
OPEN SCur
DECLARE @Sites char(1000)
DECLARE @Site varchar(100)

FETCH NEXT FROM SCur INTO @Site
Set @Sites = ''

WHILE @@FETCH_STATUS=0
BEGIN
PRINT @Site
SET @Sites = @Sites + @Site

FETCH NEXT FROM SCur INTO @Site
END

PRINT @Sites
close SCur

[Shervin Shapourian]

Daniel,

It's because you have changed the type of @Sites from VARCHAR to CHAR. When
you use CHAR your variable has trailing spaces. Even when you assign a blank
string to @Sites, it's a string of 1000 spaces. So when you try to
concatinate another string to it, the result is 1000 spaces plus @Site and
when you store it again in @Sites, SQL Server automatically trims it to fit
it into @Sites.
So, you have two options:

1. Use VARCHAR instead of CHAR.
OR
2. Trim trailing spaces before adding new site.
SET @Sites = RTrim(@Sites) + @Site

Shervin

"Daniel Fisherman" <df********@optonline.net> wrote in message
news:a9**************************@posting.google.c om...

The following code still prints an empty line when executing 'Print
@Sites'. Any ideas?
OPEN SCur
DECLARE @Sites char(1000)
DECLARE @Site varchar(100)

FETCH NEXT FROM SCur INTO @Site
Set @Sites = ''

WHILE @@FETCH_STATUS=0
BEGIN
PRINT @Site
SET @Sites = @Sites + @Site

FETCH NEXT FROM SCur INTO @Site
END

PRINT @Sites
close SCur

[Daniel Fisherman]

thanks. that worked