[RESOLVED] [2005] Returning SQLCOMMAND results to a string

I have a function that will be returning my list of customers it gets from a SQL table. But instead of just returning the results of the query in its native sql format, I need it to return them as a string, with each result after the other with a comma in the middle, like this: Customer1,Customer 2, Customer 3, etc...

I know I can use the StringBuilder to build the string, but how can I tell it that I need the results in a string?

Thanks!

See this: http://databases.aspfaq.com/general/...ingle-row.html

You won't need the string builder for this. You can make a stored procedure in your sql database with an nvarchar(max) output parameter. Then, build the delimited string within your stored procedure.

Like this

Code:

---

Set NoCount On

Declare @TestTbl Table (CustName varchar(10))

Insert into @TestTbl values ('Adams')
Insert into @TestTbl values ('Jones')
Insert into @TestTbl values ('Smith')

-- Above is just setting up some test table data

Declare @RtnString varchar(1000)

Select @RtnString=IsNull(@RtnString+', ','')+CustName
  From @TestTbl 
  Order by CustName  -- Not really a SELECT - just building a string up

Select @RtnString "Customers" -- This select returns the data out of the SPROC

---

And it looks like this

Code:

---

Customers
-------------------
Adams, Jones, Smith

---

Thats very helpful, but I'm not to familiar with stored procs, where would I put my query that selects the column into the TempTbl?
Here's the query:

Code:

---

Select Contact FROM Users WHERE Status=1 and Contact <>''

---

Ahh nevermind, I worked with it and got it working.
Here's what I did:

Code:

---

CREATE PROCEDURE
       
        dbo.ReturnEmployeesAsString

AS

        Set NoCount On
       
        CREATE TABLE #temp (EmpName varchar(40))
        Insert into #temp SELECT Contact FROM Users WHERE Status=1 and Contact <>''
               
        Declare @RtnString varchar(1500)
       
        Select @RtnString=IsNull(@RtnString+', ','')+EmpName
                From #temp
                Order by EmpName
       
        Select @RtnString "Employees"
       
        Drop table #temp
GO

---

Hmm my datatype isn't long enough. Is there a datatype that I don't have to configure a maximum length?

My temp table was for "test" purposes.

You can get rid of that and simply select directly from your table

Code:

---

CREATE PROCEDURE
       
        dbo.ReturnEmployeesAsString

AS

        Set NoCount On
       
        Declare @RtnString varchar(4000)

        SELECT @RtnString=IsNull(@RtnString+', ','')+Contact
                FROM Users WHERE Status=1 and Contact <>''
                Order by Contact
       
        Select @RtnString "Employees"
       
GO

---

I just saw your second post - make it 4000. Or even 8000.

Keep in mind that when you test it in Mgt Studio it won't show the whole column

Quote:

---

Originally Posted by szlamany

My temp table was for "test" purposes.

You can get rid of that and simply select directly from your table

Code:

---

CREATE PROCEDURE
       
        dbo.ReturnEmployeesAsString

AS

        Set NoCount On
       
        Declare @RtnString varchar(4000)

        SELECT @RtnString=IsNull(@RtnString+', ','')+Contact
                FROM Users WHERE Status=1 and Contact <>''
                Order by Contact
       
        Select @RtnString "Employees"
       
GO

---

I just saw your second post - make it 4000. Or even 8000.

Keep in mind that when you test it in Mgt Studio it won't show the whole column

---

Ahh that makes more sense :) And you're right, I'm looking at the results in Query Analyzer, probably why I'm not seeing it all.

There is a way to increase the width that QA displays - it's under options somewhere.

Stored procedures are so nice for this type of operation - remove this logic from the UI and put it in the database where it's easy to access and modify in the future.

Well it works great, in Query Analyzer. When I try to use VB.net to run the proc, it only returns '-1' even with the .ToString at the end.

Show some code please

Sorry I figured it out. I was trying to use command.ExecuteNonQuery instead of command.ExecuteScalar.

I figured it out, but thanks anyway! You've been a great help!