Solved

Returning a result cursor into comma delimited string?

Posted on 2003-11-17
7
307 Views
Last Modified: 2006-11-17
I have a stored procedure and in it, I get a cursor of about 100+ rows(only one column).
I need to concatenate each row's value into a comma delimited string. i.e. "210, 211, 212, 213.."
Is there existing method that does that?
If not, what is the data type I can use to ensure that the length of the string does not go out of bounds.
so if I declare a variable strDelimited, what data type can I use it so that I can insure its length will not be exceeded if cursor returns like 1000+ rows?

Thanks
0
Comment
Question by:StriderX
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9767769
The maximum varchar is 8000 bytes.  If you think you are going to exceed that than perhaps you should do it from the front end.

If you can post your code it shoul help.

Anthony
0
 

Author Comment

by:StriderX
ID: 9768119
DECLARE @strBFailed varchar(4000)

DECLARE @strInd varchar(10)
Set @strBFailed = ""


--FETCH ALL ROWS THAT DOES NOT MATCH WITH THE CURRENT ONE!

DECLARE curBFailed CURSOR FOR
SELECT
cast(DETAILID as varchar(10))
FROM
DETAIL b
where
status = 'Pending' and ProcessTime = @dtmTimeStamp

OPEN curBFailed
FETCH NEXT FROM curBFailed INTO @strInd
WHILE @@FETCH_STATUS = 0
BEGIN
      Set @strBFailed = @strBFailed + @strInd + CHAR(13)
      FETCH NEXT FROM curBFailed INTO @strInd
END
CLOSE curBFailed


That's the code.
How do I or what data type can I use to ensure that strBFailed doex not get exceed varchar(4000).
So basically I don't know how many rows curBfailed will return and I need to set it so that unliimited number of rows will not result in @strBFailed crashing b/c it's run out of space to hold characters.

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9768160
What about changing:
DECLARE @strBFailed varchar(4000)

To:
DECLARE @strBFailed varchar(8000)

If that is not sufficient, you will have to do it in the front end.

Anthony
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 3

Expert Comment

by:xmstr
ID: 9771845
You can do it the way StriderX recommends and it will work, but the performance will be lacking because of the use of a cursor.  The same thing can be accomplished without a cursor and will be much faster (see example below).  The string length restriction described by acperkins still applies.

declare @str varchar(8000)    
set @str = ''

select @str = @str + isnull(cast(UserNbr as varchar), '') + ','
from tblUser

-- Trim off last comma
if (right(@str, 1) = ',')
      set @str = left(@str, len(@str) - 1)

print @str

-x
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 50 total points
ID: 9774514
xmstr looks right to me.  In this particular situation you probably also want to add a length check to make sure you don't waste time processing rows that don't fit into your string anyway, for example (and going back to 4000 -- who could read more than that anyway?):

DECLARE @ids VARCHAR(4000)
SET @ids = ''

SELECT @ids = @ids + ISNULL(CAST(detailId AS VARCHAR(10)) + ','
FROM detail
WHERE status = 'Pending' AND ProcessTime = @dtmTimeStamp
AND LEN(@ids) <= 3990

SET @ids = LEFT(@ids, LEN(@ids) - 1)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9774535
Oops, the check before the LEFT is needed:

IF @ids <> ''
    SET @ids = LEFT(@ids, LEN(@ids) - 1)
0
 
LVL 3

Expert Comment

by:xmstr
ID: 9779162
I appreciate you giving someone else credit for the exact same answer I gave you!  Thanks alot! -x
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question