Menu
I have a result set in MS-SQL within a stored procedure, and lets say it has one VARCHAR column, but many rows. I want to create a comma separated string conataining all these values, Is there an easy way of doing this, or am I going to have to step through each result and build the string up manually?
Preferably I'd like to do this in the Stored Procedure itself.
228
You should populate it in your Course class and define custom toString in your Course class which you can then call toString upon. Please ensure you add java tag to your question. A ResultSet isn't a string, or anything resembling one. Logically speaking it is an array of maps.
Invoke the Statement. executeQuery method to obtain the result table from the SELECT statement in a ResultSet object. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods.
Retrieving Column Values from RowsThe ResultSet interface declares getter methods (for example, getBoolean and getLong ) for retrieving column values from the current row. You can retrieve values using either the index number of the column or the alias or name of the column. The column index is usually more efficient.
Generating a ResultSetPreparedStatement pstmt = dbConnection. prepareStatement("select * from employees"); ResultSet rs = pstmt. executeQuery(); The ResultSet object maintains a cursor that points to the current row of the result set.
Here is one way (using AdventureWorks2008 DB):
DECLARE @name varchar(255)
SET @name = NULL
select @Name = COALESCE(@Name + ',','') + LastName from Person.Person
Select @name
And here is another (for SQL 2005 onwards):
SELECT
LastName + ','
FROM
Person.Person
FOR XML PATH('')
In both cases you will need to remove the trailing comma ',' (can use STUFF() function)
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
