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

[drpcken]

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!

[nmadd]

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

[MaximilianMayrhofer]

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.

[szlamany]

Like this

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

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

[drpcken]

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:

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

[drpcken]

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

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

[drpcken]

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

[szlamany]

My temp table was for "test" purposes.

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

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

[drpcken]

My temp table was for "test" purposes.

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

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.

[szlamany]

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.

[drpcken]

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.

[szlamany]

Show some code please

[drpcken]

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!