Fetch and variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fetch and variable

Author

Topic

danyeung
Posting Yak Master

102 Posts

Posted - 2006-03-27 : 15:09:22

The first part of the following code, the print out is
32 113
55 109.5
39 105
14 102
6 65
15 47
Product ID 32,55

The last line should be Product ID 32,55,39,14,6,15.
What did I miss?

I received an error in the second part "Syntax error converting the varchar value '32,55' to a column of data type int." on intProductID not in ( @ProductID ).

How should I use @ProductID?

DECLARE @ProductIDFetch as int
DECLARE @ProjectHoursFetch as real
DECLARE @ProductID as varchar(5)
DECLARE ProductIDCursor CURSOR FOR
select top 6 intProductID, sum(realProjectHours) as ProjectHours from qryDailytimeview
where WeekendingDate = '01/08/2006' and intProductID not in ('0', '1')
group by product, intProductID
order by ProjectHours desc
OPEN ProductIDCursor
FETCH NEXT FROM ProductIDCursor into @ProductIDFetch, @ProjectHoursFetch
SET @ProductID = CAST(@ProductIDFetch AS varchar(5))

WHILE @@FETCH_STATUS = 0
BEGIN
print CAST(@ProductIDFetch AS varchar(5)) + ' ' + CAST(@ProjectHoursFetch AS varchar(5))
FETCH NEXT FROM ProductIDCursor into @ProductIDFetch, @ProjectHoursFetch
SET @ProductID = @ProductID + ',' + CAST(@ProductIDFetch AS varchar(5))
END
CLOSE ProductIDCursor
DEALLOCATE ProductIDCursor
PRINT 'Product ID ' + @ProductID

-----------------------------------------------------------------
select 'other', sum(realProjectHours) as ProjectHours from qryDailytimeview
where WeekendingDate = '01/08/2006' and intProductID not in ( @ProductID )

Thanks.
DanYeung

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-27 : 15:36:18

DECLARE @ProductID as varchar(1000)
That's why it's truncated.

You have the statement
intProductID not in ( @ProductID )

which should be
',' + @ProductID + ',' not like '%,' + convert(varchar(20),intProductID) + ',%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

danyeung
Posting Yak Master

102 Posts

Posted - 2006-03-27 : 16:03:17

Thank you so much.

Thanks.
DanYeung

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources