CODEWITHSUNDEEP
c#sqlvariables
MoonKnight
MoonKnight

Reputation: 23833

Return the Value of an SQL NVARCHAR to a C# String

All, this is something I have not had to do before and I cannot locate a simalar question for some reason. I wish to return the NVARCHAR @columnHeaders to a String in C#. The SQL query that will build the string I want is this 

DECLARE @columnHeaders NVARCHAR(MAX); 
SELECT @columnHeaders = 
    COALESCE (@columnHeaders + ',[' + Field + ']', '[' + Field + ']') 
FROM SomeTable;

However, how do I then pull the variable @columnHeaders back into C#? Note, I could use a function etc., but I do not want to use Stored Procedures, Views or Functions unless absolutely neccessary...

I can select the the columnn headers in to a DataTable using 

DECLARE @columnHeaders NVARCHAR(MAX); 
SELECT COALESCE (@columnHeaders + ',[' + Field + ']', '[' + Field + ']') 
FROM SomeTable;

and manipulate in C#, but it would be nice to return the string direct from SQL.

Thanks for your time.

Upvotes: 2

Views: 1079

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

I believe the second statement is non-set-returning, it will become an assignment process:

DECLARE @columnHeaders NVARCHAR(MAX); 
SELECT @columnHeaders = 
    COALESCE (@columnHeaders + ',[' + Field + ']', '[' + Field + ']') 
FROM SomeTable;

You have to do this, just add the selection of @columnHeaders at the last line:

DECLARE @columnHeaders NVARCHAR(MAX); 
SELECT @columnHeaders = 
    COALESCE (@columnHeaders + ',[' + Field + ']', '[' + Field + ']') 
FROM SomeTable;
select @columnHeaders as ch;

Then on your C#:

da.Fill(dt);
Console.WriteLine(dt.Rows[0]["ch"]);

Upvotes: 2

Jeff Hornby
Jeff Hornby

Reputation: 13640

You don't want to store the data in a viriable if you just want the query to return it.

Try this:

SELECT CAST(COALESCE (@columnHeaders + ',[' + Field + ']', '[' + Field + ']')  AS NVARCHAR(MAX))
FROM SomeTable;

Upvotes: 1

podiluska
podiluska

Reputation: 51494

If you don't want to use stored procedures, this will probably work.

DECLARE @columnHeaders NVARCHAR(MAX);  
    SELECT @columnHeaders =  
    COALESCE (@columnHeaders + ',[' + Field + ']', '[' + Field + ']')  
    FROM SomeTable; 
    select @columnHeaders

and use ExecuteScalar() to get the results.

However, I would personally recommend a stored procedure as being far neater, in that it keeps the SQL in the SQL Server.

Upvotes: 3

Related Questions