Looping through a stored procedure...

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)

Looping through a stored procedure...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-14 : 13:31:14

Mark writes "Hi SQL Team. Great site with loads of resources for beginner programmers like myself. Excellent work!
Hopefully you can help me out with this one.

I have a table in my database (SQL Server 2000 SP4 running on a Windows 2000 Server) holding normalized data. It looks something like this:

MemberInterests
<TR><TD>memberID</TD><TD>contentTypeID</TD></TR>
<TR><TD>2</TD><TD>1</TD></TR>
<TR><TD>2</TD><TD>5</TD></TR>
<TR><TD>6</TD><TD>1</TD></TR>
<TR><TD>6</TD><TD>2</TD></TR>
<TR><TD>6</TD><TD>3</TD></TR>
<TR><TD>6</TD><TD>4</TD></TR>

I have a stored procedure that will output one line of data in comma separated values, BUT I can only run the procedure for a single memberID (I have set memberID to '6' in this case).

Here's the stored procedure and it's output:

BEGIN

DECLARE @MemberID int
SET @MemberID = 6
DECLARE @ContentTypeID VARCHAR(255)
DECLARE @F_ContentTypeID VARCHAR(255)

DECLARE ContentTypeIdCursor CURSOR FOR
SELECT ContentTypeID FROM MemberInterests WHERE MemberID = @MemberID AND ContentTypeID IS NOT NULL

OPEN ContentTypeIdCursor
FETCH NEXT FROM ContentTypeIdCursor INTO @F_ContentTypeID
SET @ContentTypeID = @F_ContentTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM ContentTypeIdCursor INTO @F_ContentTypeID
IF @@FETCH_STATUS = 0 SET @ContentTypeID = (ISNULL(@ContentTypeID,'') + ', ' + ISNULL(@F_ContentTypeID,''))
END
CLOSE ContentTypeIdCursor
DEALLOCATE ContentTypeIdCursor
PRINT @ContentTypeID
END

Output: 1,2,3,4 (for memberID '6').

What I need to know is how do I loop through this stored procedure for each memberID in my table so I get output like this:

1,2 (where memberID = '1')
1,2,3,4 (where memberID = '6')

And so on...

Any help is greatly appreciated!
Thanks in advance.

Mark Dininio
mark@thacareguide.com"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-14 : 18:07:46

see if this help http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53885

-----------------
'KH'
if you can't beat them, have someone else to beat them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources