SQL select row into a string variable without knowing columns

Question

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. :)

Answer 1

give this a try:

DECLARE @SQL nvarchar(MAX), @YourTable nvarchar(200)
SET @YourTable='YourTableName'
SELECT @SQL=
    STUFF(
             (
                  SELECT
                  ' + '','' + COALESCE(''''''''+CONVERT(NVARCHAR(MAX),['+c.COLUMN_NAME+']'+CASE WHEN c.DATA_TYPE='datetime' THEN ',121' ELSE '' END+')+'''''''',''null'')'
                      FROM INFORMATION_SCHEMA.COLUMNS c
                      WHERE c.TABLE_NAME = 'ap_voucher'
                      ORDER BY ORDINAL_POSITION
                      FOR XML PATH('')
             ), 1, 9, ''
         )
SET @SQL = 'SELECT ' + @SQL + ' FROM '+@YourTable
exec (@SQL)

sample output:

---------------------------------------------------------------------------
'030',null,'I','Zzz0',null,'1000021111          ','2000-03-01 00:00:00.000'
'001',null,'I','zzz0',null,'900099618           ','1999-12-03 00:00:00.000'
'001',null,'I','ET10',null,'KOU557              ','1999-11-01 00:00:00.000'
'001',null,'I','Mzzz',null,'017288              ','1999-11-12 00:00:00.000'

Answer 2

To do it dynamically, just knowing the table name, you can use this approach:

DECLARE @nSQL NVARCHAR(MAX)
SELECT @nSQL = COALESCE(
     @nSQL + ' + '','' + CAST(ISNULL([' + c.COLUMN_NAME + '], '''') AS NVARCHAR(MAX))', 
    'CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX))')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'TestTable'

SET @nSQL = 'SELECT ' + @nSQL + ' FROM YourTable'

EXECUTE sp_executesql @nSQL

It depends what your end goal is though.

This will handle null values in the columns, but (e.g.) NULL integers come out as 0 for example.