SQL select row into a string variable without knowing columns

Posted by Brandi on Stack Overflow See other posts from Stack Overflow or by Brandi
Published on 2010-02-24T19:11:28Z Indexed on 2010/05/02 15:47 UTC
Read the original article Hit count: 299

Filed under:
|
|
|
|

Hello,

I am new to writing SQL and would greatly appreciate help on this problem. :)

I am trying to select an entire row into a string, preferably separated by a space or a comma. I would like to accomplish this in a generic way, without having to know specifics about the columns in the tables.

What I would love to do is this:

DECLARE @MyStringVar NVARCHAR(MAX) = ''
@MyStringVar = SELECT * FROM MyTable WHERE ID = @ID AS STRING

But what I ended up doing was this:

DECLARE @MyStringVar = ''
DECLARE @SecificField1 INT
DECLARE @SpecificField2 NVARCHAR(255)
DECLARE @SpecificField3 NVARCHAR(1000)
...
SELECT @SpecificField1 = Field1, @SpecificField2 = Field2, @SpecificField3 = Field3 FROM MyTable WHERE ID = @ID
SELECT @StringBuilder = @StringBuilder + CONVERT(nvarchar(10), @Field1) + ' ' +  @Field2 + ' ' + @Field3

Yuck. :(

I have seen some people post stuff about the COALESCE function, but again, I haven't seen anyone use it without specific column names.

Also, I was thinking, perhaps there is a way to use the column names dynamically getting them by:

SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' 

It really doesn't seem like this should be so complicated. :(

What I did works for now, but thanks ahead of time to anyone who can point me to a better solution. :)

EDIT: Got it fixed, thanks to everyone who answered. :)

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql